'SQL Server syntax HAVING COUNT

I have 3 tables, Movie, MovieLinkFile and File.

  • Movie has ID, Tile and other stuff
  • MovieLinkFile has ID, MovieID and FileID
  • File has ID, path size and other mediainfo stuff

To find the file path to a movie you would do

SELECT m.title, f.path
FROM Movie m, MovieLinkFile mlf, dbo."file" f
WHERE m.ID = mlf.movieId
  AND mlf.fileId = f.id 
  AND m.id = 7777

I need to find the movies where there is more than 1 file linked to it.

For instance movieID = 7777 has in File:

ID      Path
-------------------------------------------
1244    M:\PathTo\Title 1999\Tile.mp4 
5678    M:\OtherPathTo\Title 1999\Tile.mkv

In MovieLinkFile there is

MovieID   FileID
-----------------
  7777     1234
  7777     5678

OR FileID HAVING COUNT(*) > 1 ;

My SQL query isn't working I think because the GROUP by is wrong maybe it needs to be in a sub query?

SELECT 
    mlf.movieId, mlf.fileId, f.path 
FROM 
    movielinkfile mlf, "file" f 
WHERE 
    f.path LIKE 'M:\%'
    AND f.id = mlf.fileId
GROUP BY 
    mlf.movieId, mlf.fileId, f.path 
HAVING 
    COUNT(*) > 1 ;

Edit:

Both answers bellow work however they are both returning false positives

I'm only concerned with the movies that have more than 1 file whose path starts with 'M:' so ignoring any that have two or more files with 1 that start with 'Offline'

Unless there are 3+ files and 2+ start with 'M:'

I amended @GuidoG fiddle dbfiddle.uk
Movie ID = 111 is returning false because in MovieFile

(1, 'M:\PathTo\BestFilmEver\BestFilmEver.mkv'),

(2, 'OffLine\BestFilmEver.dvd') Hope that makes sense

sqlsql-server


Solution 1:[1]

That could be written in a number of ways. One of them is:

SELECT mlf.movieId, mlf.fileId, f.path 
FROM movielinkfile mlf
inner join [file] f on f.id = mlf.fileId
inner join (select Movieid, count(*) movies
from Movielink
group by MovieId) ml on mlf.movieId = ml.movieId
where ml.Movies > 1 and f.Path like 'M:\%';

Solution 2:[2]

The COUNT(*) applies to each group.

You are grouping by "mlf.movieId, mlf.fileId, f.path". Since both the "mlf.fileId" and "f.path" are different for each of the 2 matches, this will create separate groups. So each group will have a COUNT(*) of 1.

If you want to find the movies with multiple files, you need to clean up the groups.

For example

SELECT mlf.movieId, COUNT(*) AS number_of_files
FROM   movielinkfile mlf, "file" f
WHERE  f.path LIKE 'M:\%'
AND    f.id = mlf.fileId
GROUP BY mlf.movieId
HAVING COUNT(*) > 1

If you need the actual "files" data of such movies, you could go for something like this

SELECT ...
FROM Movie m, MovieLinkFile mlf, "file" f
WHERE m.ID = mlf.movieId
AND   mlf.fileId = f.id 
AND (
  SELECT COUNT(*)
  FROM   movielinkfile mlf2, "files" f2
  WHERE  mlf2.fileId = f2.id
  AND    mlf2.movieId = m.ID
) > 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Cetin Basoz
Solution 2

Related Questions