Sql + asp

[DaveRook]

Hello

I am trying to build an SQL table for cars. For this example, there are only 2 cars.

One of the cars comes in 1 colours, the other in 2, but there is no limit to the amount of colours (and each car have the same colour as another car etc). For this reason, I am using 2 tables. 1, for the car make and if it is petrol/diseal, the other table lists the colours available.

Using a join, I can create a view which allows me access to the table.

http://www.daverook.com/table.jpg shows the SQL tables (if my explanation wasn't useful). In this example all the colours are different but in the full database the colours are used multiple times

So, now to the ASP bit!

I want to display the 2 cars on my asp page (as a list)(C01 and C02) but as you can see I have 3 rows (C01, C01 and C02).

The issue is:
My asp page allows customers to filter by petrol/diseal and by colour so I need to join the 2 tables.

However, I don't want to list the same car twice which the join displays.

What is the best way around this? As there can be an almost unlimited amount of colours (eg, midnight blue, sky blue, sea blue etc) I can't add them as new columns!

Any suggestion is much appreciated

[jhardman]

If I understand your predicament correctly (you have a many-to-many relationship) then there needs to be an additional table for you to join. It should list

 car1 | blue
 car2 | green
 car1 | green

does this make sense?

Jared

[DaveRook]

Hi Jared

I don't think I explained it well - however, the result you've shown me is the result I get in my SQL database after the join...

table1
ID   CarID      petrolType    Doors
1     car01       petrol        5
2     car02       diesel        4

table2
ID   CarID    Colour
1     car01    blue
2     car01    green
3     car02    green

An inner join of
select * from table1 LEFT OUTER JOIN table2 ON table1.CarID = table2.CarID

and this creates a similar view (result) as your reply

view1
ID   CarID      petrolType    Doors      Colour
1     car01       petrol        5         blue
2     car01       petrol        5         green
3     car02       diesel        4         green

However, if I now list the results on a webpage, using
DIM variable = "%"
"SELECT * FROM view1 WHERE Colour LIKE '"+variable+ "'"

(yes, I know to use SQL paramaters. It is a variable as it will take parameters from a querystring (I know about SQL injections))

It will now list 3 items where as I only want 2 items to be listed on my asp page (car01 and car02) but I want all the colours of the car to be listed within the 2 items.

So, my web page would show:

car01, petrol, 5, green, blue
car02, diesel, 4, green

I assume I need a loop within a loop? Or do you know of another way to achieve this. Could you also clarify that this is a one to many relationship?

Thanks

Dave

[viktorka]

I had similar cases and I did not use loop in loop. First I would make sure that SQL is sorted. Then I would set CarIDprevious variable to store the CarID from the previous record. Then you may compare the current CarID with previous CarID and if it is the same then you sum colors.

[DaveRook]

Viktorka

Thank you for your response. Are you saying you do all of this within the SQL? If so, how does it remember the previous records ID?

Thank you

[viktorka]

No I do it in asp - it's much faster. If you do it in SQL it will take more machine resources.