HELP….

I’m having trouble getting data out of two MSSql Tables…
These tables are linked by a car_id

Version table
Car_id Make Model Series
1 Ford KA 1.1
2 Ford Puma 1.3

Options table
Car_id Option_ID Option Text
1 21001 Aircon
1 21002 CD Player
2 21002 CD Player

But what I want to do is pull out of both tables and return the results but I don’t think this is possible…

eg
Car_ID Make Model Series AC CD
1 Ford KA 1.1 Aircon CD Player
2 Ford Puma 1.3 - CD Player

This is the code iv created so far but it does not return the right results.. Basically if its got Aircon and a cdplayer its in the list if not then it gets dropped… and why of stopping this…(3902 in the version table 3106 in the results…)

SELECT dbo.version.id_128 AS make, dbo.version.id_129 AS model, dbo.version.id_131 AS Series, Aircon.content AS AC, cd.content AS CDPlayer
FROM dbo.version INNER JOIN
dbo.standard_text Aircon ON dbo.version.vehicle_id = Aircon.vehicle_id INNER JOIN
dbo.standard_text cd ON dbo.version.vehicle_id = cd.vehicle_id
WHERE (Aircon.language_id = 1) AND (Aircon.schema_id = 21001) AND (cd.language_id = 1) AND (cd.schema_id = 1301)


Thought of adding blank records to the Options table for the right options id and card id but could not work out a way of doing it automaticly..

Any one got any ideas… You will help a bloke from going MAD!!!!!!!!!!.

Sorry for the long post…!!!
ROB

I think you need to use a case statement in the select.

Can't exactly remember the syntax or whether its ansi standard though!

Basically...

select blah, blah2 = case A: do this b: do this
from blah


etc.

HTH!

Rich

try...

select a.ID_128 as MAKE, a.id_129 as MODEL, a.id_131 as SERIES, b.CONTENT as OPTION from VERSION a left outer join OPTIONS b on a.CAR_ID = b.CAR_ID

this would igve you a list.. e.g.

Ford Ka 1.1 Aircon
Ford Ka 1.1 21002 CD Player
Ford Puma 1.3 21002 CD Player

If you want the results like you have them in your post you'd need to do...

select a.ID_128 as MAKE, a.id_129 as MODEL, a.id_131 as SERIES, b.CONTENT as CDPPLAYER, c.CONTENT as AC
from VERSION a, OPTIONS b, OPTIONS c
where a.CAR_ID *= b.CAR_ID
and a.CAR_ID *= c.CAR_ID
and b.OPT_CODE = 1
and c.OPT_CODE = 2

The problem with this is you need to add an instance of the OPTIONS table for every option on the car, so in the case of a BMW etc you could end up with lots and lots of instances of the OPTIONS table, which is a bit crap for performance. Better to do it the first way and write a little routine to set out the data how you want it.


Doh... edited to say the OPT_CODE is a unique value for each row in the opitons table.. e.g. Aircon is 1, CD Player is 2, Eleccy Windows would be 3 etc...


[Edited by Sparks - 4/16/2002 1:16:16 PM]

ok.. I have also tried this and can't find any string aggregation functions however your best bet is to process the results outside of mysql.

There is a function I remember called something like TRANSPOSE but that may be a wild goose chase. If only sum() would take string parameters eh ;)
soz :(

ok I've found the function "CONCAT" but I dont know if it will work as an aggregate function. you'd have to try it (I dont have access to mysql here @ work)

i.e. Select Model,concat(Features) from Cars inner join options on cars.id=options.id group by model

probably won't work but worth a try :) I bet it just says "concat is not part of an aggregate function" blah blah blah