[SOLVED] Join with case --> help!

[gr00]

Dear group,

 

I need some expert help..

We have three tables; comments, cars and bikes.

 

cars (list of cars)

----

car_id, primary key

car_name, string

 

bikes (list of bikes)

----

bike_id, primary key

bike_name, string

 

comments (comments on cars or bikes)

----

comment_id, primary key

reference_id, reference to car or bike

reference_type, domain (car/bike)

comment, string

comment_dt, time of the comment

 

I need to show the most recent comments. For this, I need a query that joins comments on cars and bikes. I think I need to use a case statement, because sometimes the reference_id references a car (reference_type=car), and sometimes a bike (reference_type=bike).

 

I can think of this:

 

select
  com.*, car.car_name, bik.bike_name
from
  comments com,
  cars car,
  bikes bik
where
  CASE WHEN com.comment_type = 'C' THEN com.reference_id = car.car_id
            WHEN com.comment_type = 'B' THEN com.reference_id = bik.bike_id
  END
order by
  com.comment_dt desc

 

Unfortunately, this doesn't work. Is there an expert who can help me?

 

Bast regards,

gr00

 

 

(MySQL 5.0.24-standard)

[effigy]

I would create a separate SQL statement for each, then UNION them together.

[akitchin]

although this doesn't directly answer your question, why not aggregate your vehicles into one table rather than have two tables?  this lends itself to new vehicle types automatically, without requiring a new table:

 

vehicles
vehicle_id PK INT
vehicle_type INT
vehicle_name STRING

vehicle_types
type_id PK INT
type_name STRING

 

you could even do away with the types table altogether.

[gr00]

Wow, that are quick answers!

The UNION is what I need. Aggregating both tables into one is not possible, because the tables have different columns. For simplicity, I didn't mention them.

 

Thanks,

gr00