Can anyone help me with a SQL-query? [message #22626] |
Sat, 19 October 2002 13:45 |
Tom-Erik Valsoe
Messages: 1 Registered: October 2002
|
Junior Member |
|
|
Hello!
I want to make a query from table below.
It it to get the personIds for the persons that are also casts in ALL the movies where he/she is director.
I don't want the directors that have only been 'cast' in one or some of the movies where he/she is director.
In the table below, the query is to return only personId 111111, not 111111 and 20574(for this person has only been 'cast' in one of the two movies that he/she has directed.)
Can anyone help me out?
Regards,
Tom-Erik
PERSONID PARTNAME FILMID
---------- ------------------------------ ----------
111111 cast 111444
111111 director 111444
333111 director 555444
250575 cast 555444
20574 director 908070
20574 director 302010
20574 cast 302010
114422 director 676767
|
|
|
Re: Can anyone help me with a SQL-query? [message #22630 is a reply to message #22626] |
Sat, 19 October 2002 23:04 |
PRASHANT SHEKHAR
Messages: 13 Registered: October 2002
|
Junior Member |
|
|
select personid,filmid,
sum(decode(partname,'directtor',1,'cast',1,0))
from table
group by personid, filmid
having mod(sum(decode(partname,'directtor',1,'cast',1,
0)),2) = 0
|
|
|