Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 3rd, 2003, 05:18 PM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Table Joins

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..
 
Old June 3rd, 2003, 06:15 PM
Authorized User
 
Join Date: Jun 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
 
Old June 3rd, 2003, 06:24 PM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
optimizing MySQL table joins - script running slow crmpicco MySQL 7 January 31st, 2006 10:54 AM
SQL - Help with Joins oldmuttonhead Classic ASP Databases 0 January 21st, 2006 05:02 PM
SQL Joins in XPath arcuza XSLT 0 March 16th, 2005 01:05 PM
Union joins in SQL sellis Access 3 April 7th, 2004 12:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.