如何基于列值联接不同的表 [英] How to JOIN on different tables based on column value

问题描述

广告表格
截屏http://img90.imageshack.us/img90/6295/adsvo.png

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

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

我有3个桌子的广告，汽车和电话.

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`

**我无法在您的任何帖子中添加评论，但我希望它可以根据ads表中的类别自动添加评论.

** 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`

推荐答案

要买车:

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

获取电话:

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

要同时使用UNION:

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

注意:我建议您在汽车和电话表中设置外键，以引用ads.ID，而不要使用子表的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

这篇关于如何基于列值联接不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！