Sky Sky - 3 months ago 7
MySQL Question

how to match the subquery field?

I using subquery to count the total items.

my sql query like this.

SELECT `id`,`name`,
(SELECT count(*) FROM `tags_r` WHERE tag_id = `t`.`id`) as count
FROM `tags` AS `t`


This sub query is work.

but if I need to get count = 0. How can I do?

I had try it but not work.

test 1 - not work

SELECT `id`,`name`,
(SELECT count(*) FROM `tags_r` WHERE tag_id = `t`.`id`) as count
FROM `tags` AS `t` WHERE count > '0'


test 2 - not work

SELECT `id`,`name`,
if (SELECT count(*) FROM `tags_r` WHERE tag_id = `t`.`id`) > '0'
FROM `tags` AS `t`


but not work.

Answer

Try it with the JOIN logic instead:

SELECT 
  t.id,
  t.name,
  COUNT(tr.tag_id) AS count
FROM tags t
LEFT JOIN tags_r tr ON tr.tag_id = t.id
GROUP BY t.id, t.name
HAVING COUNT(tr.tag_id) = 0

But if you are looking to return records from tags table where there are no matching rows in tags_r table given the condition tags_r.tag_id = tags.id then you could use the NOT EXISTS:

SELECT 
  t.id,
  t.name
FROM tags t
WHERE NOT EXISTS (
  SELECT 1
  FROM tags_r tr
  WHERE tr.tag_id = t.id
  )