CODEWITHSUNDEEP
mysqlsql
Leo Jiang
Leo Jiang

Reputation: 26105

How can I return unique rows when using an INNER JOIN in MySQL?

I have 3 tables: links, keywords, and keywords_links. A link can have multiple keywords, and multiple links can have the same keyword. keywords_links is used to join links and keywords. When I run the following query, multiple rows of the same link is returned because multiple keywords match. How can I modify this query to return unique rows?

SELECT links . * 
FROM  `links` 
INNER JOIN keywords_links ON keywords_links.link_id = id
INNER JOIN keywords ON keywords.id = keywords_links.keyword_id
WHERE keywords.keyword
IN (
'php', 'sql', 'html', 'css'
)

Upvotes: 0

Views: 95

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

The easiest way would be to put a DISTINCT after the SELECT:

SELECT DISTINCT links.*
...
...

But perhaps a more efficient solution would be to rewrite your query so that it doesn't have to make the distinction across EVERY column:

SELECT a.*
FROM links a
INNER JOIN
(
    SELECT a.link_id
    FROM keywords_links a
    INNER JOIN keywords b ON
        a.keyword_id = b.id AND
        b.keyword IN ('php', 'sql', 'html', 'css')
    GROUP BY a.link_id
) b ON a.id = b.link_id

Upvotes: 1

Related Questions