Provided this sort of db structure how might one write a query to find only movies that have both male and female patrons?
movie
movID
name
ticket
TID
movID
patronID
patron
PID
gender
-
stackoverflow.com/questions/19499472/…SemperFi– SemperFi2014-04-29 17:47:47 +00:00Commented Apr 29, 2014 at 17:47
-
What do you have so far? If you can join the tables to get effectively all patrons for all movies, you can count the number of distinct genders for each one?Alex Poole– Alex Poole2014-04-29 17:48:07 +00:00Commented Apr 29, 2014 at 17:48
-
I've gotten to joining the tables but I'm unsure of how to go about the counting step you're describing.sonobenissimo– sonobenissimo2014-04-29 18:10:13 +00:00Commented Apr 29, 2014 at 18:10
Add a comment
|
3 Answers
I'd say do two queries to get all male patrons and female patrons and then join those based on movID:
WITH malePatrons AS
(
SELECT name, m.movID
FROM movie JOIN ticket tic ON movie.movID = tic.movID
JOIN patron pat ON pat.PID = tic.patronID
WHERE pat.gender = "male"
), femalePatrons AS
(
SELECT name, m.movID
FROM movie JOIN ticket tic ON movie.movID = tic.movID
JOIN patron pat ON pat.PID = tic.patronID
WHERE pat.gender = "female"
)
Select * FROM malePatrons JOIN femalePatrons fem ON malePatrons.movID = fem.movID
Comments
You don't need to query the tables multiple times. My comment may have been a but too obscure, but I was trying to hint you towards something like:
select m.name
from movie m
join ticket t on t.movid = m.movid
join patron p on p.pid = t.patronid
group by m.movid, m.name
having count(distinct p.gender) = 2;
This looks for all tickets for all movies, and counts the number of distinct genders of the patron who had those ticketss. (This assumes there are only two genders of course, for simplicity; if you have more then you can add a filter). The having clause checks that the count is 2. If a movie only has male or female patrons, not both, the count would be 1, and it would be excluded from the result.
Comments
Select m.movieid, name, count(distinct(gender))as Count
from
MOVIE m
JOIN
Ticket t
on m.movid=t.movid
JOIN
patron P
on
t.patronid=p.Pid
GROUP BY m.movieid, name
HAVING count(distinct(gender)) > 1
