Jump to content

[SOLVED] Join with case --> help!


gr00

Recommended Posts

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.