0
\$\begingroup\$

I am trying to count the titles according to the first letter and filter it by the tag.

I tried for ages and I am not sure if I can write this query in a better way. Can you please advise me.

Here is the sqlfiddle link: http://sqlfiddle.com/#!9/3155cd/2

My query:

SELECT letter.letter as firstLetter, COUNT(item_tag.id) AS itemCount
        FROM letter
        left outer join item
             on left(item.title, 1) = letter.letter
        left outer join item_tag
            ON item_tag.item_id = item.id
        AND item_tag.tag_id = 1
        GROUP BY letter.letter

Here is my tables:

Item:

+----+-----------------+
| id |      title      |
+----+-----------------+
|  1 | title goes here |
+----+-----------------+

tag:

+----+----------+
| id |   name   |
+----+----------+
|  1 | tag name |
+----+----------+

item_tag

+----+--------+---------+
| id | tag_id | item_id |
+----+--------+---------+
|  1 |      1 |       1 |
+----+--------+---------+

letter:

+----+--------+
| id | letter |
+----+--------+
| 1  | A      |
| 2  | B      |
| .  | .      |
+----+--------+
\$\endgroup\$
0
\$\begingroup\$

Fiddle is currently broken again, but you should do the count as early as possible -> Inner instead of more expensive Outer join and less rows to join.

SELECT l.letter AS firstLetter
  -- return 0 for missing letters
  ,Coalesce(lc.cnt,0) AS itemCount
FROM letter AS l
LEFT OUTER JOIN 
 ( -- count letters before join
   SELECT Left(i.Title,1) AS letter
     ,Count(*) AS cnt
   FROM item AS i -- no Outer Join needed
   JOIN item_tag AS it
     ON it.item_id = i.id
    AND it.tag_id = 1
   GROUP BY Left(i.Title,1)
 ) AS lc
ON lc.letter = l.letter
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.