How to JOIN on different tables based on column value

Question

ads table
Screenshot http://img90.imageshack.us/img90/6295/adsvo.png

phones table
Screenshot http://img194.imageshack.us/img194/3713/phones.png

cars table
Screenshot http://img35.imageshack.us/img35/1035/carsm.png

i have 3 tables ads,cars and phones.

i want to join tables is based on category in ads table.

and i tried this query but no luck,any helps?

SELECT *
  FROM `ads` 
  JOIN `ads.category` ON `ads.id` = `ads.category.id`

** i cant add comment any of your post,but i want it to be automatic based on category in ads table.

for example :- if in table have phones category,i will automatic join phones table then

SELECT *
  FROM `ads` 
  JOIN `phone` ON `ads.id` = `phone.id`

if in table have cars category,i will automatic join cars table

SELECT *
  FROM `ads` 
  JOIN `cars` ON `ads.id` = `cars.id`

Answer 1

to get the cars:

SELECT *
FROM ads JOIN cars ON ads.ID = cars.ID

to get the phone:

SELECT *
FROM ads JOIN phones ON ads.ID = phones.ID

to get both use a UNION:

SELECT ads.ID, ads.Title, ads.Desc, ads.category, 'Car' AS AdType, cars.Year AS Col1, cars.cc AS Col2, cars.transmission AS Col3
FROM ads JOIN cars ON ads.ID = cars.ID
UNION ALL SELECT ads.ID, ads.Title, ads.Desc, ads.category, 'Phone' AS AdType, phone.Model AS Col1, phone.Color AS Col2, '' AS Col3
FROM ads JOIN phones ON ads.ID = phones.ID

note: i would advise you to setup foreign keys in your cars and phones tables, to reference the ads.ID instead of using the ID field (Primary key?) of the child tables

Answer 2

SELECT      ads.*,
            cars.year, cars.cc, cars.transmission,
            phones.model, phones.color
FROM        ads
  LEFT JOIN cars
  ON        cars.id = ads.id
  LEFT JOIN phones
  ON        phones.id = ads.id

Though i agree with @Scrum, you can left join them, then (in your code) use the type field and retrieve the correct columns.

I also feel like these columns should be NULLABLE and all in one table. I don't see the point in breaking these off in their own with a 1-1 relation (and direct key relationship). This certainly isn't normalization (that I can see). maybe I'm tired and inept