[thelist]

dr Dave drdave at unknowngenius.com
Fri Jul 16 02:40:37 CDT 2004


Hello Everybody,

First time poster... think this is on topic, all apologies if it's 
not...

I am currently pulling my hair trying to put together a query in mysql. 
It sounded rather trivial, but have been completely stuck on it for a 
while now...

I have a bunch of tables all linked together one-to-many and I want, 
among other things, filter out my results depending on a field in the 
*last* joined table not matching a certain value. So far, easy 
enough... but the catch is: if that value is present for any given row, 
I want to exclude all the rows linked from the same first table's 
index.

Example: I am selecting from 'x' LEFT JOIN 'y' ... LEFT JOIN 'z'
I'll get a result set like somewhat like that:
x.id	y.id	z.id	z.foobar
1	1	1	0
1	1	2	1
1	2	1	0
1	2	3	2
2 etc.

Now, I want to be exclude *every* row with x.id = 1 depending on the 
value of z.foobar for *any* row where x.id =1
Say I choose to filter out z.foobar = 1, then this query should return 
no row with x.id = 1. If I choose foobar = 3, I should get at least one 
row (or many: it doesn't matter since I'm applying a DISTINCT on the 
whole query and only care about x.id)...
If I add a "z.foobar != 1", I will still get three rows with x.id = 1 
(I want none)...

I'm generating and calling that query from PHP, but doing the filtering 
in PHP is not really an option for many reasons... because of the way 
it's generated, it would also be appreciable if I can keep the "LEFT 
JOIN 'next_table'" structure... but at this point I'll take any 
suggestion for a SQL query that could do that...

thanks a lot in advance for any help or suggestion!
-- 
Dave / デイヴ
drdave at unknowngenius.com



More information about the thelist mailing list