Database Discussion - SQL Table Joins

Print Page | Close Window

SQL Table Joins

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=3249
Printed Date: 29 March 2026 at 1:00pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Table Joins

Posted By: Dredd
Subject: SQL Table Joins
Date Posted: 03 June 2003 at 1:39pm

Hey...

i got 3 tables in my dvd database..

dvd

movieid

title

year

director

actor

actorID

actor

movies

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...

Actor Movie Year

Robert DeNiro Heat 2000

Al Pacino Heat 2000

this is what ive asked it to do of course but what i really want is to see it all on one record like....

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..

Replies:

Posted By: michael
Date Posted: 03 June 2003 at 1:58pm

Has nothing to do with Distinct, the easiest way I think would be to dynamically construct the table in asp when retrieving the record, so only writing to a new row when the movie title changed. You could put all that into a SP if you use sqlserver but nevertheless i would do it all in asp. Despite all that, your database design is not very good, there are more efficient ways to store the info you have.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Dredd
Date Posted: 03 June 2003 at 2:03pm

well no excuses for db design as i am a completer and utter Newbie at sql..

could someone give me an example of a better design?.. it would be a great help..

Posted By: ljamal
Date Posted: 03 June 2003 at 4:39pm

The basic design of the database is good, but you may want to separate the Actors' name into more than one field so you can sort by the last names.

The best way to build the table is add a new row for each movie and just append to the last cell if only the actor has changed.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Dredd
Date Posted: 03 June 2003 at 5:05pm

Thanks Ljamal,..

thats exactly what i want to do .. but how?..

how do i add a new row per movie instead of adding new row per record?.

Posted By: ljamal
Date Posted: 03 June 2003 at 9:57pm

for the SQL statement I would use:
SELECT movies.movieID, dvd.Title, dvd.year, actor.Actor
FROM dvd INNER JOIN (actor INNER JOIN movies ON actor.actorID=movies.ActorID) ON dvd.movieID=movies.movieID
WHERE dvd.title='heat';

Then use the following code:

<table>
<%
movieID = 0
Do while not rs.eof
if movieID<>rs(0) then
movieID = rs(0)
response.write "<tr><td>"&rs(1)&"</td><td>"&rs(2)&"</td></td>"&rs(4)
else
response.write ", "&rs(4)
end if
rs.MoveNext
if rs.eof then
response.write "</td></tr>"
elseif rs(0)<>movieID then
response.write "</td></tr>"
end if
Loop
%>
</table>

That should give you an idea of how it is to be done.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: MorningZ
Date Posted: 04 June 2003 at 6:41am

you definitely cant do what you describe in just a simple SQL statement, if its SQL server, you can use a cursor though (bear with me)

CREATE PROCEDURE [dbo].[GetMovieList]

DECLARE @MovieID int, @Movie varchar(255), @Year int
DECLARE @Actors varchar(1000), @ThisActor varchar(200)

SET NOCOUNT ON
CREATE TABLE #tempList ( MovieID int, Movie varchar(255), [Year] int, Actors varchar(1000) )
DECLARE c1 CURSOR FOR
SELECT movieID, title, [year] FROM dvd
OPEN c1
FETCH NEXT FROM c1 INTO @MovieID, @Movie, @Year
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Actors = ''
DECLARE c2 CURSOR FOR
  SELECT actor FROM actor WHERE actorID IN
   (SELECT actorID FROM movies WHERE movieID = @MovieID)
OPEN c2
FETCH NEXT FROM c2 INTO @ThisActor
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Actors <> ""
   BEGIN
    SET @Actors = @Actors + ', '
   END
  SET @Actors = @Actors + @ThisActor
FETCH NEXT FROM c2 INTO @ThisActor
END
CLOSE c2
DEALLOCATE c2

INSERT INTO #tempList
(MovieID, Movie, Actors) VALUES
(@MovieID, @Movie, @Actors)
FETCH NEXT FROM c1 INTO @MovieID, @Movie, @Year
END

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF
SELECT * FROM #tempList

-------------
Contribute to the working anarchy we fondly call the Internet