SQL Server syntax HAVING COUNT-CodePudding

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

CodePudding user response：

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:\%';

CodePudding user response：

Another way is to find the movies with more than one file first, and then join with File

You can try it yourself in DBFiddle

select t.Id as MovieId,
       mf.Path
from   ( select m.Id
         from   Movie m
           inner join MovieLinkFile mlf on m.Id = mlf.MovieId
         group by m.Id
         having count(1) > 1
       ) t
   inner join MovieLinkFile mlf on t.Id = mlf.MovieId
   inner join MovieFile mf on mlf.FileID = mf.Id

It basically is doing the same as the answer from @cetin-basoz but with a little different approach. It depends what you find the easiest to read and understand

CodePudding user response：

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 ..., movielinkfile mlf
WHERE EXISTS (
  SELECT COUNT(*)
  FROM   "files" f
  WHERE  f.id = mlf.fileId
  HAVING COUNT(*) > 1
)