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
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
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