MySQL:Get records with some related records,but not others

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL:Get records with some related records,but not others

Post by batfastad »

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

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` ASC
But what's the best way to add the "but don't have tag_id 717 selected" bit?
I've tried just adding:

Code: Select all

AND `tags_data`.`tag_id` NOT IN ('717')
... 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
Last edited by batfastad on Fri Sep 23, 2011 12:52 pm, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL:Get records with some related records,but not othe

Post by VladSun »

batfastad wrote: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?
[sql]SELECT
`companies`.`company_name`,
`companies`.`email`
FROM
`companies`
INNER JOIN
`tags_data` ON
`companies`.`company_id` = `tags_data`.`company_id`
AND
`tags_data`.`tag_id` = 724
LEFT JOIN
`tags_data` AS `exclude_tags` ON
`companies`.`company_id` = `exclude_tags`.`company_id`
AND
`exclude_tags`.`tag_id` = 623
WHERE
`exclude_tags`.`tag_id` IS NULL[/sql]
Last edited by VladSun on Thu Sep 09, 2010 4:28 pm, edited 3 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL:Get records with some related records,but not others

Post by VladSun »

Additionally, if you want to exclude several tags you will need to use "id in (...)" in the left join, GROUP BY company id and remove rows with count(excluded id) > 0 by using a HAVING clause. The WHERE clause should be removed in this case.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL:Get records with some related records,but not others

Post by batfastad »

Hi Vladsun
Ok that's excellent. Seems to be working great. I would not have got there myself.
And if I think about it, I can understand how/why it works :lol: (but then I lose it and get confused again ;))

But one question... how can I return companies who have both tag_id 724 and 717
Is it 2 INNER JOINS?
Or the 1st an INNER and the 2nd LEFT?

Cheers, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL:Get records with some related records,but not othe

Post by VladSun »

If you use the same approach ( "in (...)" ) with required tags then:
1.

Code: Select all

HAVING
    count(required_id) > 0
is for OR operator applied to "in ( id_1, ... id_N)"

2.

Code: Select all

HAVING
    count(required_id) = N
is for AND operator applied to "in ( id_1, ... id_N)"

That's true if you have only one tag_data record per company_id,tag_id - which should be the case.
Last edited by VladSun on Fri Oct 14, 2011 1:45 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL:Get records with some related records,but not others

Post by batfastad »

That's absolutely brilliant! :lol:
Thanks so much Vladsun
Post Reply