Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: Showing records that have multiples enteries in a field

    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?

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    May 2004
    Posts
    3
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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...
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    May 2004
    Posts
    3
    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

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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>"
    %>
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •