[thelist] SQL query problem...

drDave drdave at unknowngenius.com
Fri Jul 16 04:27:03 CDT 2004


> Could you post an example of your query?


Mattias,

Sorry for that omission, I had figured it might be easier to give a 
simplified example than the actual query...

Here is a live example... as I said earlier, the JOIN logic is a bit 
strange due to the query being dynamically generated in PHP using form 
data... but I'll be happy with any query that does something along the 
line of what I want...

  SELECT DISTINCT `people`.`people_id` AS `id`, 
CONCAT(`people`.`first_name_eng`, " ", `people`.`last_name_eng`, " (", 
`people`.`last_name_kanji`, " ", `people`.`first_name_kanji`, ")") AS 
`name`, `companies`.`name_eng` AS `company_name`
FROM `people` LEFT JOIN `companies` ON `people`.`company_id` = 
`companies`.`company_id` LEFT JOIN `item_processed_people` ON 
`item_processed_people`.`people_id` = `people`.`ERM_code` LEFT JOIN 
`item_processed` ON `item_processed`.`proc_id` = 
`item_processed_people`.`proc_id` LEFT JOIN `marketing_items` ON 
`marketing_items`.`marketing_id` = `item_processed`.`marketing_id` ON  
`marketing_items`.`marketing_id` != '25'
WHERE 1 AND `marketing_items`.`marketing_id` IS NULL ORDER BY 
`people`.`last_name_eng`

I am trying to get every `people_id` for which *none* of the joined 
`marketing_id` is 25... but...
As you can see... no matter how I rearrange the ON and WHERE conditions 
on `marketing_id`, I will still get every `people_id` for which one row 
has a value different from 25 for its `marketing_id` field...

Hope this is clear enough...

-- 
dr Dave

On Jul 16, 2004, at 6:01 PM, Mattias Thorslund wrote:

> 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!
>
>
>
> -- 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>
>
>
-- 
Dave / デイヴ
drdave at unknowngenius.com



More information about the thelist mailing list