special sql query

[clochiani]

Hello,

I have two tables

tblMovie (MovieID, Name, ImdbNumber)
tblOtherTitle (MovieID, Name)

A movie has a name and can also have other names (kept in the second table).

I want to generate a list of movies and for each movie I need all the other names. I must create a sql query that does the job. Is it possible?

 

[clochiani]

I'm using SQL Server 2000

 

[PHV]

Something like this ?
SELECT MovieID, Name FROM tblMovie
UNION
SELECT MovieID, Name FROM tblOtherTitle
ORDER BY 1
Or this ?
SELECT M.MovieID, M.Name, M.ImdbNumber, O.Name
FROM tblMovie M LEFT JOIN tblOtherTitle O
ON M.MovieID = O.MovieID
ORDER BY M.MovieID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244