CODEWITHSUNDEEP
mysqlsqlcountmatch
bagofmilk
bagofmilk

Reputation: 1550

MySQL SELECT Count of existence for each ID

I have a table of images and I have another table of image tags.

I'm trying to write a SQL query that will get the number of valid tags per Image ID.

My initial attempt was this:

SELECT b.ID AS ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a, tbl_images b 
WHERE (
    b.ID = a.IMG_ID AND 
      (a.TAG_NAME LIKE '%scenic%' OR 
       a.TAG_NAME LIKE '%fruit%' OR 
       a.TAG_NAME LIKE '%bench%' OR 
       a.TAG_NAME LIKE '%bird%'
      )
);

The result was this:

ID    NUM
7      13

However, what I want is to show the count value of tags for EACH Image ID found. What I'm looking for is something more like this:

ID    NUM
3      2     -- Image ID #3 has 2 tags that match the query
68     1     -- Image ID #68 has 1 tag that matches the query
87     3     -- ...
92     2     -- ...
187    1     -- ...
875    2     -- ...

Upvotes: 1

Views: 80

Answers (4)

Richard
Richard

Reputation: 109015

  1. Prefer the newer (since the 90's!) join syntax

    SELECT b.ID AS ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a
 INNER JOIN tbl_images b on b.ID = a.IMG_ID
…

as it is much clearer.

  1. To group when using aggregates use GROUP BY

    SELECT a.IMG_ID AS ID, COUNT(a.ID) AS NUM
FROM tbl_tags a
WHERE a.TAG_NAME LIKE '%scenic%' 
  OR a.TAG_NAME LIKE '%fruit%' 
  OR a.TAG_NAME LIKE '%bench%'
  OR a.TAG_NAME LIKE '%bird%'
GROUP BY a.IMG_ID

Note you don't really need the join here as you're not using anything from the tbl_Images.

Upvotes: 1

Hussein Salman
Hussein Salman

Reputation: 8236

SELECT a.IMG_ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a
WHERE a.TAG_NAME LIKE '%scenic%' OR 
       a.TAG_NAME LIKE '%fruit%' OR 
       a.TAG_NAME LIKE '%bench%' OR 
       a.TAG_NAME LIKE '%bird%'      
Group by a.IMG_ID

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

Here is an option using REGEXP which cleans up the WHERE clause a bit:

SELECT
    a.ID,
    COUNT(b.ID) AS NUM
FROM tbl_images a
INNER JOIN tbl_tags b
    ON a.ID = b.IMG_ID
WHERE
    b.TAG_NAME REGEXP 'scenic|fruit|bench|bird'
GROUP BY
    a.ID;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You don't need a join at all for this, because the image id is in tbl_tags:

SELECT t.IMG_ID AS ID, COUNT(*) AS NUM
FROM tbl_tags t
WHERE t.TAG_NAME LIKE '%scenic%' OR 
      t.TAG_NAME LIKE '%fruit%' OR 
      t.TAG_NAME LIKE '%bench%' OR 
      t.TAG_NAME LIKE '%bird%'
GROUP BY t.img_id;

I strongly advise you to use abbreviations for table names (such as t for tbl_tags) rather than arbitrary letters. It makes the query much easier to follow.

Upvotes: 0

Related Questions