Showing records that have multiples enteries in a field

stadler · #1 (**permalink**) 05-04-04, 09:57

Not sure if the title is worded well....

Anyway, I've just started using ASP. I've got a MS Access database that contains three tables. tblCar contains the fields Car_ID, Car_Name. tblColour contains Colour_ID and Colour and tblCarColour contains Car_ID and Colour_ID.

I know how to connect to the database, get information and show it using ASP. My problem is when trying to show multiple entries. For example, in the above, I want to be able to show Car_Name and all the colours which that car comes in. I've been trying to achieve this for a few hours now but I've got nothing to show for it. Anyone care to push me in the right direction?

Seppuku · #2 (**permalink**) 05-04-04, 13:18

You just need to do a Join.

Code:

SELECT * FROM tblCar t1, tblColour t2, tblCarColour t3 WHERE t1.Car_ID = t3.Car_ID AND t2.Colour_ID = t3.Colour_ID

The values t1, t2, and t3 are Aliases of the tables tblCar, tblColour, and tblCarColour (respectively). You are joining the Car ID to a record in tblCarColour which is then joined to the colors in tblColour.

There are more complex Joins you can do such as Left or Right Outer Joins, but I think for your question, a simple Inner Join is all that's required.

stadler · #3 (**permalink**) 05-04-04, 14:47

Thanks for the reply.

I've got that far. My problem is in getting this information displayed using ASP. I am fine in displaying the information of a single or group of records (for example, showing Car_ID and Car_Name of all the Cars in the database) but I'm stuggling when one of these fields (in the case I detailed, all the colours a particular car can come in) can possibly contain more than one entry.

Seppuku · #4 (**permalink**) 05-04-04, 19:34

Exactly how would you like the data to be represented... give me an example of how you'd expect the data to be displayed, and I'll see if I can mimic it with SQL/ASP...

stadler · #5 (**permalink**) 05-04-04, 19:49

OK. Italics used to indicate table heading. Just using the code tags for formatting reasons

Code:

Car       Colours available
Ferrari   Red, Black, Yellow
Porsche   Silver, Black, Green

or

Code:

Car         Colours Available 
Ferrari     Red
            Black
            Yellow

Seppuku · #6 (**permalink**) 05-05-04, 02:33

Yeah.. use that same SQL

SELECT Car, Colour FROM tblCar t1, tblColour t2, tblCarColour t3 WHERE t1.Car_ID = t3.Car_ID AND t2.Colour_ID = t3.Colour_ID

But in your ASP, just check for the Car to change. RS = ADO Recordset Object

Code:

<%
  Response.Write "<table>"
  Dim sCar, sPrevCar
  While NOT RS.EOF
    sCar = RS("Car")
    Response.Write "<tr><td>"
    If sCar <> sPrevCar Then
      Response.Write RS("Car")
    Else
      Response.Write "&nbsp;"
    End If
    Resposne.Write "</td><td>" & RS("Colour") & "</td></tr>"
    sPrevCar = sCar
    RS.MoveNext
  WEnd
  Response.Write "</table>"
%>

Internet Services

Web Development

Digital Marketing

Consumer Tech