[SOLVED] What is the wrong with this query?

Issue

I have 3 sql tables calls category, movies, category_movies. category and movies tables have many to many relationship. Thats why I use category_movies table. This is tables’ structure…

Category : cat_id, cat_name, 
movies : mov_id, mov_name, 
category_movies : cat_id, mov_id

Now I have got 3 category IDs dynamically and now I want to select movies’ names alone with category names which belongs to 3 category_id have already got.

This is the query that I tried so far..

SELECT  c.cat_name AS cn, m.mov_name AS mn, m.mov_id 
FROM    category AS c 
        INNER JOIN category_movies AS cm ON cm.cat_id = c.cat_id        
        INNER JOIN movies AS m ON m.mov_id = cs.mov_id
WHERE   c.cat_id IN (2, 5, 7)
GROUP BY c.cat_name, m.mov_name, m.mov_id 
HAVING COUNT(*) >= 3

but this is now working.. can anybody tell me what is wrong with this query?

Solution

use IN clause on this

SELECT..
FROM..
WHERE cat_id IN (2, 5, 7)

and is the same with

SELECT..
FROM..
WHERE cat_id = 2 OR
      cat_id = 5 OR
      cat_id = 7

Please also take note that it is INNER JOIN not INNOR JOIN

but I guess, you want to perform RELATIONAL Division (you want to search for a movie that has all the category you want to find)

SELECT  c.cat_name, m.mov_name, m.mov_id 
FROM    category AS c 
        INNER JOIN movies AS m ON m.cat_id = c.cat_id
        INNER JOIN category_movies AS cm ON cm.mov_id = m.mov_id
WHERE   cat_id IN (2, 5, 7)
GROUP BY c.cat_name, m.mov_name, m.mov_id 
HAVING COUNT(*) >= 3

• Relational Division

Answered By – John Woo

Answer Checked By – Mildred Charles (BugsFixing Admin)