CODEWITHSUNDEEP
mysqljoin
Deniz Zoeteman
Deniz Zoeteman

Reputation: 10111

WHERE with joined tables

I basicly have a query similiar to this:

SELECT * FROM news
LEFT JOIN tags ON tags.newsid = news.id

Now let's say, that I want to get all news rows which have a tag (row in tags table) that's tags.name value is "test" and another tag that's tags.name value is "test2". So basicly, I would like to check for two values in a joined table, if they are both in there, then give back that news row.

How would this be possible? Sorry if this has been asked before, I don't really know what kind of keywords I would use in my search for this particular question, since it's quite complicated.

Upvotes: 0

Views: 58

Answers (3)

Nathan Harkenrider
Nathan Harkenrider

Reputation: 566

Using a subquery to identify the relevant rows in the tag table should give you the results you're seeking.

SELECT * FROM news n
INNER JOIN (SELECT newsid FROM tags WHERE tags.name in ('test1', 'test2')) t
ON n.id = t.newsid

Upvotes: 0

Barmar
Barmar

Reputation: 780724

SELECT news.*, COUNT(*) c
FROM news
INNER JOIN tags ON tags.newsid = news.id
WHERE tags.name IN ("test", "test2")
HAVING c = 2

I'm assuming the combination of tags.newsid, tags.name is unique.

Here's how you do what you asked for in the comment.

SELECT news.*, COUNT(*) c
FROM news n
INNER JOIN (
    SELECT tags.newsid newsid
    FROM tags
    WHERE tags.name = "test"
    UNION ALL
    SELECT DISTINCT tags.newsid 
    FROM tags
    WHERE tags.name IN ("test2", "test3")
) t
ON t.newsid = n.id
HAVING c = 2

Another way to do it is:

SELECT DISTINCT news.*
FROM news n
JOIN tags t1 ON n.id = t1.newsid
JOIN tags t2 ON n.id = t2.newsid
WHERE t1.name = "test"
AND t2.name IN ("test2", "test3")

This latter method is easier to extrapolate to arbitrary combinations.

Upvotes: 5

wxyz
wxyz

Reputation: 707

Try this:

  select * from news where 
(select count(*) from tags  where tags.newsId = news.id and tags.name='test')=1 and 
(select count(*) from tags where tags.newsId = news.id and tags.name='test2')=1

Upvotes: 0

Related Questions