Page 1 of 1

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

Posted: Sat Jan 31, 2009 4:12 am
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

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

Posted: Sat Jan 31, 2009 5:51 am
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]

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

Posted: Sat Jan 31, 2009 6:08 am
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.

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

Posted: Sat Jan 31, 2009 7:54 am
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

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

Posted: Sat Jan 31, 2009 8:59 am
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.

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

Posted: Sat Jan 31, 2009 11:29 am
by batfastad
That's absolutely brilliant! :lol:
Thanks so much Vladsun