CODEWITHSUNDEEP
sqloracle-database
sonobenissimo
sonobenissimo

Reputation: 301

SQL Multiple Values in Same Column

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

Upvotes: 0

Views: 138

Answers (3)

Sun21
Sun21

Reputation: 34

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

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191570

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.

SQL Fiddle demo.

Upvotes: 0

rickkr
rickkr

Reputation: 144

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

Upvotes: 2

Related Questions