MySQL:Get records with some related records,but not others
Posted: Sat Jan 31, 2009 4:12 am
Hi everyone
I'm having a bit of trouble getting a particular query worded here, I hope someone can help me out.
We have a companies table, a tags_data table and a tags table
companies is related to tags_data by company_id
tags_data is related to tags by a tag_id. So tags_data only has 2 fields: company_id and tag_id so it sits between the companies and tags tables in the structure.
It's basically a way of marking companies as a MANUFACTURER, PRESS, DISTRIBUTOR, DEALER etc, or all of those. So you can have multiple tags for each company.
And I'm looking to get a sub-set of companies, say all those who are marked as MANUFACTURER, but aren't marked as DISTRIBUTOR
Here's my query to get all MANUFACTURERS, tag_id 724
But what's the best way to add the "but don't have tag_id 717 selected" bit?
I've tried just adding:
... to my WHERE clause.
But that just gives me the same result count... The way my query is worded there'll only ever be one tag_id per record in tags_data, so that is to be expected really.
But does anyone know what I should be doing to get all companies with a certain tag_id (the query above), but exclude those that also have tag_id 623 selected in another record in tags_data?
Cheers, B
I'm having a bit of trouble getting a particular query worded here, I hope someone can help me out.
We have a companies table, a tags_data table and a tags table
companies is related to tags_data by company_id
tags_data is related to tags by a tag_id. So tags_data only has 2 fields: company_id and tag_id so it sits between the companies and tags tables in the structure.
It's basically a way of marking companies as a MANUFACTURER, PRESS, DISTRIBUTOR, DEALER etc, or all of those. So you can have multiple tags for each company.
And I'm looking to get a sub-set of companies, say all those who are marked as MANUFACTURER, but aren't marked as DISTRIBUTOR
Here's my query to get all MANUFACTURERS, tag_id 724
Code: Select all
SELECT `companies`.`company_name`, `companies`.`email` FROM `tags_data` LEFT JOIN `companies` USING (`company_id`) WHERE `tags_data`.`tag_id`='724' ORDER BY `companies`.`company_name` ASCI've tried just adding:
Code: Select all
AND `tags_data`.`tag_id` NOT IN ('717')But that just gives me the same result count... The way my query is worded there'll only ever be one tag_id per record in tags_data, so that is to be expected really.
But does anyone know what I should be doing to get all companies with a certain tag_id (the query above), but exclude those that also have tag_id 623 selected in another record in tags_data?
Cheers, B