[thelist]

Mattias Thorslund mattias at inreach.com
Fri Jul 16 04:01:28 CDT 2004


Could you post an example of your query?

dr Dave wrote:

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





More information about the thelist mailing list