SQL Table Joins

Dredd · June 3rd, 2003, 05:18 PM

Hey...
i got 3 tables in my dvd database..

[u]dvd </u>
        movieid
        title
        year
        director

[u]actor</u>
        actorID
        actor

[u]movies</u>
        movieID
        actorID

now when i run a query like...

SELECT actor.Actor, dvd.Title, dvd.year
FROM dvd INNER JOIN (actor INNER JOIN movies ON actor.actorID=movies.ActorID) ON dvd.movieID=movies.movieID
WHERE dvd.title='heat';

i get the result...

Robert DeNiro Heat 2000
Al Pacino Heat 2000

when what i really want is.....

Heat 2000 Robert DeNiro Al Pacino

how do i go about that?..

do i use a DISTINCT command?.. i guess i cant really as they're not duplicate records... if i cant change that in SQL can i display it that way somehow in ASP?...

ta..

Blaise · June 3rd, 2003, 06:15 PM

In ASP you can pretty much display your results any way you wish

Let's say you got something like this...

Code:

'This is all your DB stuff
SQL = "SELECT * FROM Query_movie WHERE title ='Heat'"
SET Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open SQL, objConn

'Here goes what you see on screen
Response.Write(Rs("title") & " " & Rs("year"))
DO WHILE NOT Rs.EOF
  Response.Write(" " & Rs("actor"))
  Rs.MoveNext
LOOP

That will stick them all in a line.

Hope this helps!

Dredd · June 3rd, 2003, 06:24 PM

Blaise..

Thankyou i knew there would be an easy way of displaying them in ASP but i had a mind block .. thanks for your help...

:D