CODEWITHSUNDEEP
mysql
panthro
panthro

Reputation: 24061

Inner Join returning no results

I need to get rows out from article and users table when the slug appears in my highlight table.

Highlight Table

id | slug
 1   blue
 2   green

Article Table

id | slug | title
1    blue
2    pink

User Table

id | slug | name
1    blue
2    green
3    brown

Heres my query:

SELECT slug from highlight_table
INNER JOIN article_table ON highlight_table.slug = article_table.slug
INNER JOIN user_table ON highlight_table.slug = user_table.slug

I would hope to get id 1 from article table and id 1 and 2 from users table.

The issue is Im getting nothing back from the query.

Upvotes: 0

Views: 2443

Answers (2)

R. Chappell
R. Chappell

Reputation: 1282

The query has an error because your SELECT slug is ambiguous. Your column slug appears in all of your tables so MySQL doesn't know which column to return. You need to do

SELECT `highlight_table`.`slug` from `highlight_table`

This will tell MySQL to only return the slug column from the highlight_table.

You should then only get 1 row which is blue, because blue exists in all three tables. Changing to LEFT JOIN for both article and user tables would get you 2 results back (green and blue) as INNER JOIN basically works as an AND and LEFT JOIN works more like an OR

Update!

Based on the final lot of information here is a query that does work:

SELECT highlight.slug from highlight
    LEFT JOIN article ON highlight.slug = article.slug
    LEFT JOIN user ON highlight.slug = user.slug
WHERE
    article.slug IS NOT NULL OR user.slug IS NOT NULL

Another example of doing this:

SELECT `highlight`.`slug` from `highlight`
WHERE `highlight`.`slug` IN (SELECT `user`.`slug` FROM `user` UNION SELECT `article`.`slug` FROM `article`)

OR

SELECT `highlight`.`slug` from `highlight`
INNER JOIN (SELECT `user`.`slug` FROM `user` UNION SELECT `article`.`slug` FROM `article`) AS `allslugs` ON `highlight`.`slug` = `allslugs`.`slug`

Another update, I call this one "fun with joins"

SELECT `highlight`.`slug` from `highlight`
    RIGHT JOIN `user` ON `highlight`.`slug` = `user`.`slug`
    LEFT JOIN `article` ON `highlight`.`slug` = `article`.`slug`
WHERE
    `highlight`.`slug` IS NOT NULL

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Try changing your query to qualify the column name in select list

SELECT h.`slug` from HighlightTable h
INNER JOIN ArticleTable a ON h.`slug` = a.`slug`
INNER JOIN UserTable u ON h.`slug` = u.`slug`;

Can't reproduce the issue. See This Fiddle

per your latest comment you need a LEFT JOIN query like

SELECT h.`slug` from HighlightTable h
LEFT JOIN ArticleTable a ON h.`slug` = a.`slug`
LEFT JOIN UserTable u ON h.`slug` = u.`slug`;

Then do a separate JOIN and UNION the result set

    SELECT h.`slug` from HighlightTable h
    INNER JOIN ArticleTable a ON h.`slug` = a.`slug`
   UNION
    SELECT h.`slug` from HighlightTable h
    INNER JOIN UserTable u ON h.`slug` = u.`slug`;

Upvotes: 0

Related Questions