Re: Tricky database query involving two tables, much more detail

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. - Ken kenzo@free-music.com >If you have known relationship between the tables shared between the queries then selecting >"b.bar!=1" should actually work. > >Hard to get real specific though without seeing a "real" query and table schema.