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