Re: [PHP-DB] Tricky database query involving two tables, much more detail
>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. You might be able to get away using something like this though it will not be usable with large tables without some other limitations applied: SELECT user.name FROM user LEFT OUTER JOIN thing ON user.id=thing.user GROUP BY user.id HAVING thing.id != "10" It depends on whether the "HAVING" still gets to see all the results or whether it only gets what is left at the "top" of the "GROUP BY" and also whether having the single entity in the set is enough to removes the entire "GROUP BY" set. Worth a try though. Failing that you will have to simulate a sub query either by using a second table or by using some PHP logic on the results. If the numbers are small, you could do this with PHP without much hassle (lots of nice array functions). Cheers, Ron - Island Net AMT Solutions Group Inc. Telephone: 250 383-0096 1412 Quadra Toll Free:1 800 331-3055 Victoria, B.C. Fax:250 383-6698 V8W 2L1 E-Mail:[EMAIL PROTECTED] Canada WWW: http://www.islandnet.com/ - -- 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]
Re: [PHP-DB] 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. 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]
Re: [PHP-DB] 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 [EMAIL PROTECTED] >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. -- 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]
Re: [PHP-DB] Tricky database query involving two tables
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 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. Cheers - Island Net AMT Solutions Group Inc. Telephone: 250 383-0096 1412 Quadra Toll Free:1 800 331-3055 Victoria, B.C. Fax:250 383-6698 V8W 2L1 E-Mail:[EMAIL PROTECTED] Canada WWW: http://www.islandnet.com/ - -- 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]
[PHP-DB] Tricky database query involving two tables
I have a tricky database query I can't figure out how to write. I have a PHP app using MySQL that does a query like this: (main query) select * from A left join B on something where B.thing = "1" This gets me everything in A where its corresponding element in B has a certain attribute. What I want is to be able to now do this: select * from A where A not in the result set of the main query I can't just do: select * from A left join B on something where B.thing != "1" or B.thing is NULL because things in A might occur more than once in B, (or not at all in B, but that's covered by the NULL part), and so this query would return things in A that I already got in the main query. 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)? Let me know if I need to be more clear or specific in my question. Thanks! - Ken [EMAIL PROTECTED] -- 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]
