mysql - 根据条件从不同的表中选择

Question

我有一张 table

ABC (id, type_id, x, y, z)

这 5 个表:

 x(id, title, address_id, my-X-Related-Values)
 y(id, title, address_id, my-Y-Related-Values) 
 z(id, title, address_id, my-Z-Related-Values)
 address(id,address)
 type(id,title)

ABC中的x,y,z分别指的是x.id,y.id,z.id。

注意，ABC 的每一行中只有 x 或 y 或 z 具有值，其他均为 NULL。

输入表格:

1. id:1 ---- title:'hello x'
2. id:2 ---- title:'你好'
3. id:3 ---- title:'hello z'

到目前为止我已经完成了此操作，但不知道如何继续处理 y 和 z 表:

SELECT ABC.id ,type.title AS type,address.address FROM ABC
INNER JOIN address,type,x,y,z
WHERE ABC.x = x.id AND 
address.id = x.address_id

编辑:我有这六个查询，我只想在一个查询中执行:

            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,apartment.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,apartment,deal_type,melk_types WHERE melk_standardize.apartment_id = apartment.apartment_id AND bongah.id = apartment.bongah_id AND deal_type.id = apartment.deal_type AND melk_types.id = 1 ORDER BY apartment.apartment_id";
            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,apartment_presale.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,apartment_presale,deal_type,melk_types WHERE melk_standardize.apartment_presale_id = apartment_presale.apartment_presale_id AND bongah.id = apartment_presale.bongah_id AND deal_type.id = apartment_presale.deal_type AND melk_types.id = 2 ORDER BY apartment_presale.apartment_presale_id";
            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,house.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,house,deal_type,melk_types WHERE melk_standardize.house_id = house.house_id AND bongah.id = house.bongah_id AND deal_type.id = house.deal_type AND melk_types.id = 3 ORDER BY house.house_id";
            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,garden.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,garden,deal_type,melk_types WHERE melk_standardize.garden_id = garden.garden_id AND bongah.id = garden.bongah_id AND deal_type.id = garden.deal_type AND melk_types.id = 4 ORDER BY garden.garden_id";
            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,shop.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,shop,deal_type,melk_types WHERE melk_standardize.shop_id = shop.shop_id AND bongah.id = shop.bongah_id AND deal_type.id = shop.deal_type AND melk_types.id = 5 ORDER BY shop.shop_id";
            $string = "SELECT melk_standardize.melk_id ,bongah.title AS bongah,deal_type.title AS deal,ground.address AS address,melk_types.title AS type FROM melk_standardize
INNER JOIN bongah,ground,deal_type,melk_types WHERE melk_standardize.ground_id = ground.ground_id AND bongah.id = ground.bongah_id AND deal_type.id = ground.deal_type AND melk_types.id = 6 ORDER BY ground.ground_id";

Answer 1

为每个表添加单独的联接并指定每个联接的条件。我假设您想要在 x、y 和 z 上加入 ABC，其中 ABC 是任何 x、y 或 z 匹配项。

不确定这是否能给你真正想要的东西，但我认为它已经很接近了。

SELECT ABC.id AS id, type.title AS type, address.address AS address
 FROM ABC
    JOIN address ON (ABC.id = address.id)
    JOIN type ON (ABC.id = type.id)
    JOIN x ON (ABC.x is NULL OR ABC.x = x.id)
    JOIN y ON (ABC.y is NULL OR ABC.y = y.id)
    JOIN z ON (ABC.z is NULL OR ABC.z = z.id)