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

Ta



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]

AS

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

GO

 

 

 



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



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net