Self Join Problem 1

[Ludo4ka]

Hi,

I have a table

Table A

Movie Code Date
Matrix 1 12/03/2003
Matrix 2 12/25/2003
Matrix2 5 05/03/2002
Matrix3 1 04/09/1999
Matrix3 2 03/09/1998
Matrix4 1 06/09/1997

What i need the query to return is:

Movie Date Date
Matrix 12/03/2003 12/25/2003
Matrix3 04/09/1999 03/09/1998

So basically I need the query to check where the code for the same movie equals 1 & 2 and return the name of the movie and the Date of both codes 1 & 2.

I can't get this to work. What I have is

SELECT c.Name, c.Date, b.Date
FROM a as c, a as b
WHERE (c.Code=1 AND b.Code= 2);

The result I get is:

Movie Date Date
Matrix 12/03/2003 12/25/2003
Matrix3 04/09/1999 12/25/2003
Matrix4 06/09/1997 12/25/2003
Matrix 12/03/2003 04/09/1999
Matrix3 04/09/1999 04/09/1999
Matrix4 06/09/1997 04/09/1999

Please somebody help...

Thanks
Ludo4ka

 

[r937]

[tt]select t1.Movie
, t1.Date as Date1
, t2.Date as Date2
from A as t1
inner
join A as t2
on t1.Movie = t2.Movie
where t1.Code=1
and t2.Code=2 [/tt]

rudy

http://r937.com/

 

[Ludo4ka]

Hey Rudy,

Thank you so much for the answer!
God, I feel kinda stupid, this is an easy problem and I couldn't solve it...

Thanks again
And have a happy new year

Luda