Basic Query Help (Pivots)

[madmike117]

Hey. For some reason I am having issues with some joins ...

 

Table 1 (videos) - id|video_data|etc

1|blah|blah

2|blah|blah

3|blah|blah

 

Table 2 (keywords) - id|keyword

1|word 1

2| word 2

3| word 3

4| word 4

5| word 5

 

Table 3 (keywords_videos) keyword_id|video_id

1|1

1|2

1|3

2|1

2|2

3|1

3|2

3|3

3|4

 

I am trying to get a count of the number of videos with certain words ...

 

So, I want to take the keywords_videos and use it to link 1 video to many keywords ...

 

SELECT COUNT(*) as Total FROM videos

  JOIN keywords_videos ON videos.id = keywords_videos.video_id

  JOIN keywords ON keywords_videos.keyword_id = keywords.id

  WHERE keywords.word IN('word 1','word 2')

 

I get a count for each video and the # of keywords it has as opposed to the total videos found where it has a matching word ...

 

I guess Im having a terrible mental lapse this morning.

 

[DavidAM]

SELECT COUNT(*) as Total FROM videos

  JOIN keywords_videos ON videos.id = keywords_videos.video_id

  JOIN keywords ON keywords_videos.keyword_id = keywords.id

  WHERE keywords.word IN('word 1','word 2')

 

I get a count for each video and the # of keywords it has as opposed to the total videos found where it has a matching word ...

 

You shouldn't be getting a count for each video AND the # of keywords ... you only return 1 column.  Using your sample data, I think you are getting 6 (3 with 'word1' and 3 with 'word2'). If you changed it to IN ('word1', 'word3'), I think you would get 5. That is still more videos than you have in your table.  If what you want is the number of unique videos that have any of those keywords, I think you need to use:

 

SELECT COUNT(DISTINCT video_id) as Total 
   FROM keywords_videos ON videos.id = keywords_videos.video_id
   JOIN keywords ON keywords_videos.keyword_id = keywords.id
   WHERE keywords.word IN('word 1','word 2')

You don't really need the videos table in the query, you just want a count of the IDs.  Using COUNT(DISTINCT) will only count each video_id once regardless of how many of the keywords it has.