select count(table) problem

[ret4rt]

Hello.
I have a database with movies similar with imdb and i want to find out which directors have directed both thriller and drama movies.
The output i want is like this "DIR_NAME,amount of thriller movies,amout of drama movies"
First i did a check to see the expected number.Checkin only directors that have made thriller movies with

select DIR_NAME, count(GENRE) 
from MGENRES, DIRECTORS, DIRECTED_BY
where GENRE = 'Thriller' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
group by DIR_NAME
order by count(GENRE) desc
 

I found 3600 results and max count per director 36,Doing the same with keyword drama i found 19000 results and max count per director 200.So results must be 3600 or less and max count 200.
I use this query for finding out directors taht have made thriller and drama movies

select DIR_NAME, count(t1.GENRE) as Thriller, count(t2.GENRE) as Drama
from MGENRES as t1, MGENRES as t2, DIRECTORS, DIRECTED_BY as t3, DIRECTED_BY as t4
where t1.GENRE = 'Thriller' and t1.MOVIE_ID = t3.MOVIE_ID and t3.DIR_ID = DIRECTORS.DIR_ID and t2.GENRE = 'Drama' and t2.MOVIE_ID = t4.MOVIE_ID and t4.DIR_ID = t3.DIR_ID
group by DIR_NAME
order by count(t1.GENRE) desc 
 

The directors results seems good but the count is for sure wrong.I get the same count for each row count(t1.GENRE) = count(t2.GENRE) and very big amounts that can't be true 1000 thriller movies directed by one director etc which is fault.
Any clue?

[gateshosting]

I don't think that will work because you are still doing a record count on the rows, which because you have joined the 2 tables for thriller and Drama. They row will give you a count of 1 no matter what genra it is.

Try something like this:

SELECT
  DIR_NAME,
  SUM(CASE WHEN [T1].[GENRE] = 'Thriller' THEN 1 ELSE 0) AS THRILLERS,
  SUM(CASE WHEN [T1].[GENRE] = 'Drama' THEN 1 ELSE 0) AS DRAMAS
FROM
  MGENRES [M] LEFT OUTER JOIN
  DIRECTEDBY [DB] ON [M].[MOVIE_ID] = [DB].[MOVIE_ID] LEFT OUTER JOIN
  DIRECTORS [D] ON [DB].[DIR_ID] = [D].[DIR_ID]
GROUP BY DIR_NAME
 

This would give you the count for each of these genres. However, I would suggest creating a view of the count of each genre/director... then join that view.

SELECT
  COUNT(*),
  GENRE,
  DIR_ID,
  DIRECTOR_NAME
FROM
  MGENRES M LEFT OUTER JOIN
  DIRECTEDBY D ON M.DIR_ID = D.DIR_ID LEFT OUTER JOIN
  DIRECTORS DRS ON D.DIR_ID = DRS.DIR_ID
GROUP BY
  DIR_ID, DIRECTOR_NAME, GENRE
 

I only showed you the first way so you can have some creative ideas for future scripts. The second is the best way to do it. You can even build it into a stored procedure to pass dir_id, or genre... It would be quicker.

Best regards,

Michael C. Gates

[ret4rt]

Thx for reply.I understand ur 2nd query but your 1st didn't get it.Tried it and gave me errors.The 2nd one is working ok.I thought it like u said with views and so i created two views.One that contains THRILLERS and the other DRAMAS,
I did it like this

create view DRAMAS as
select DIR_NAME, count(GENRE) as DramaCount , DIRECTORS.DIR_ID
from MGENRES, DIRECTORS, DIRECTED_BY
where GENRE = 'Drama' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
group by DIR_NAME , DIRECTORS.DIR_ID
 
create view THRILLERS as
select DIR_NAME, count(GENRE) as ThrillerCount , DIRECTORS.DIR_ID
from MGENRES, DIRECTORS, DIRECTED_BY
where GENRE = 'Thriller' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
group by DIR_NAME , DIRECTORS.DIR_ID
 
 
select distinct DRAMAS.DIR_NAME, ThrillerCount, DramaCount
from DRAMAS, THRILLERS
where DRAMAS.DIR_ID = THRILLERS.DIR_ID
group by DRAMAS.DIR_NAME , ThrillerCount , DramaCount
order by ThrillerCount desc
 

As you see then i use a select to output what i want.Seems ok now to me.Hope i'm not mistaken :)

[gateshosting]

Glad to help. I think you can do this with one view though. It would group it by Director / Genre, then you can do a select count(*) from myView where genre = x, etc. Once you have the view, you can summarize the data further. The view is like a temporary table. Structured the same way.

Best regards,

Michael C. Gates