>At 07:04 PM 2/28/01 -0800, Ron Brogden wrote:
>  >At 09:50 PM 2/28/2001 -0500, you wrote:
>  >>Is there a way to do this in MySQL?  Or do I have to just query 
>everything, and then have the skipping logic be in PHP?  I'd love to 
>encapsulate this in a query.  Would it involve subqueries (something 
>I know that MySQL doesn't directly support)?
>  >
>  >The part that seems to be missing here is that you are note 
>limiting the relationships between table A and table B, you are just 
>slapping them together.  There should probably be a field that you 
>use to lock rows together between the tables:
>  >
>  >select a.foo,b.bar from A left outer join B ON a.foo=b.foo where b.bar=1
>
>Thanks, Ron...
>Actually, I am doing that (I had implied it when I said "on something"):
>select * from A left join B on something
>where B.thing != "1" or B.thing is NULL
>
>The problem, again, is that this gets me things that are in B that 
>don't have a certain attribute...but what I want is things that 
>aren't in the set of (has certain attribute).
>
>Here's some sample data to illustrate what I'm talking about:
>
>Table A:
>fields: nameid, name
>1, Joe
>2, Mary
>3, Bob
>4, Jim
>
>Table B:
>fields: thing, nameid (used in join)
>10, 1
>10, 2
>11, 1
>11, 3
>
>My main query was this (and I'll add the details this time):
>select name from A left join B on A.nameid = B.nameid
>where B.thing = "10"
>
>This returns:
>Joe, Mary
>
>Now, what I want is to be able to have a query that returns 
>everything in A that DIDN'T come back in that query.  Specifically:
>Bob, Jim
>
>I can't query where B.thing != "10", because that will include the 
>"11,1" entry, which gets me "Joe", which I don't want.
>
>The bad query I listed before (again, with details added) is:
>select name from A left join B on A.nameid = B.nameid
>where B.thing != "10" or B.thing is NULL
>
>This gets me:
>Joe, Bob, Jim
>
>No good, since I don't want Joe there.
>
>The issue, again, is that it's not good enough to say what doesn't 
>match the "thing" in B.  Things may occur again and again in B, both 
>matching "thing" and not matching "thing".  I want to know 
>specifically the opposite of the first query (i.e. the opposite of 
>"what does match the "thing" in B"), which is a bit different.
>
>Thanks much for any help!  I've been going nuts trying to solve this one.

Sir, load the results of the first query into a TEMPORARY table, and 
then run a difference query on A and the temp table.

Bob Hall

Know thyself? Absurd direction!
Bubbles bear no introspection.     -Khushhal Khan Khatak

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to