[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