Mysql doesnt query

Question

I have 2 tables like this. I wanna make a query which is Select a Dvd only has Indian subtitle.

  CREATE TABLE dvd(
 `Id` INT NOT NULL,
 `Name` VARCHAR(45) NULL,
 `Category` VARCHAR(45) NULL,
  `Price` INT NOT NULL,
  PRIMARY KEY (`Id`));  

 CREATE TABLE Subtitles(
 `Id` INT NOT NULL,
 `Language` VARCHAR(45) NULL);

I'm trying to do this but this query selects every DVD has a Indian subtitles.

SELECT Name FROM DvD JOIN Subtitles 
ON  Dvd.Id=Subtitles.ID 
where Language='Indian' GROUP BY Name HAVING COUNT(*) = 1;

Answer 1

The query doesn't work because the where clause is applied before the grouping. So you get all the rows where Language='Indian', and then, every group has just one row.

One trick is to check that the maximum and minimum language are the same (meaning that there's just one row in the group), and that they are Indian. Note that the where clause should be removed:

SELECT   d.id
FROM     dvd d
JOIN     subtitles s ON  d.id = s.id
GROUP BY id
HAVING   MAX(language) = MIN(language) AND MAX(language) = 'Indian'