>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]
