CODEWITHSUNDEEP
mysql
Miha Šušteršič
Miha Šušteršič

Reputation: 10052

mysql SELECT column only if exists

I have 5 tables I want to select data from, but sometimes the 5th table (named comment) will be empty. When that happens I want my query to return null for the values from that table, and only return the other values (the 5th table includes user comments, sometimes there are none).

Here is my query:

SELECT articles.title, articles.posted, articles.body, authors.name, authors.img, authors.bio, comment.user_id, comment.text, comment.article_id, GROUP_CONCAT(categories.cat_name) AS cat_name
FROM articles, authors, categories, article_categories, comment 
WHERE articles.author_id = authors.id
AND articles.id = article_categories.article_id
AND article_categories.category_id = categories.id
AND articles.id = comment.article_id
AND title LIKE :title;

the :title comes from PDO and is not relevant here.

The problem comes from the AND articles.id = comment.article_id. I don't know how to write it in a way to only check for this, if it's there and ignore it otherwise.

Thanks for the help

Upvotes: 0

Views: 301

Answers (2)

Diego Furtado
Diego Furtado

Reputation: 11

You should use left/right joins to do that.

[...]
select *
from articles a
  left join comments c
    on a.id = c.article_id
[...]

To learn more about joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins

Upvotes: 0

dotnetom
dotnetom

Reputation: 24901

You should use proper join syntax, and in this case instead of INNER JOIN use LEFT JOIN for comment table:

SELECT articles.title, articles.posted, articles.body, authors.name, authors.img, authors.bio, comment.user_id, comment.text, comment.article_id, GROUP_CONCAT(categories.cat_name) AS cat_name
FROM articles INNER JOIN authors ON articles.author_id = authors.id
INNER JOIN article_categories ON articles.id = article_categories.article_id
INNER JOIN categories ON article_categories.category_id = categories.id
LEFT JOIN comment ON articles.id = comment.article_id
WHERE title LIKE :title;

Upvotes: 1

Related Questions