mysql - 如何根据列值加入不同的表

Question

广告表
Screenshot http://img90.imageshack.us/img90/6295/adsvo.png

电话表
Screenshot http://img194.imageshack.us/img194/3713/phones.png

汽车表
Screenshot http://img35.imageshack.us/img35/1035/carsm.png

我有 3 个表格广告、汽车和电话。

我想加入基于广告表中类别的表。

我试过这个查询但没有成功，有什么帮助吗？

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

** 我无法对您的任何帖子添加评论，但我希望它根据广告表中的类别自动添加。

例如:-如果表中有电话类别，我将自动加入电话表

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

如果表中有汽车类别，我会自动加入汽车表

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

Answer 1

获取汽车:

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

拿到电话:

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

要同时使用 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

注意:我建议您在 cars 和 phones 表中设置外键，以引用 ads.ID 而不是使用子表的 ID 字段(主键？)