CODEWITHSUNDEEP
mysqlsqlsubquery
zppinto
zppinto

Reputation: 297

Create one single query from 2 queries

I have this two queries:

SELECT COUNT(*) as cnt, video.title
FROM tags
INNER JOIN tag_name ON tags.tag_name_id = tag_name.tag_name_id 
INNER JOIN video ON video.filename = tags.filename 
WHERE tag_name.tag LIKE 'tree' 
GROUP BY filename

AND

SELECT COUNT(*) as cnt2 FROM tags GROUP BY filename

On the first one, I have a counter that counts the number of times the tag "tree" appear in each individual video.

On the second one, I select the total of tags from each video.

The results can be founded here: 1st query <--> 2nd query

Now I'm trying to "join" them, by using the subquery approach, but I don't came with the right results... The counters are not work as I expected, as you can see here

What am I doing wrong? And how can I "join" this two queries as one correctly?

Upvotes: 1

Views: 49

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Try this (SELECT COUNT(*) AS cnt2 FROM tags WHERE filename=t.filename) vcnt by using corelated subquery will count all the records from tags where subquery's filename is equal to t.filename parent table's filename i.e tags t

SELECT 
  COUNT(*) AS cnt,
  (SELECT 
    COUNT(*) AS cnt2 
  FROM
    tags 
  WHERE filename = t.filename) vcnt,
  video.title 
FROM
  tags t 
  INNER JOIN tag_name 
    ON t.tag_name_id = tag_name.tag_name_id 
  INNER JOIN video 
    ON video.filename = t.filename 
WHERE tag_name.tag LIKE 'tree' 
GROUP BY t.filename

Upvotes: 1

Related Questions