我有这样的表：
Table1 Table2
name1 | link_id name2 | link_id
text 1 text 2
text 2 text 4
我想得到结果：
name1 name2 link_id
text text 1
text text 2
text text 4
我怎么能这样做？
加：
Sry,我的英语不太好.我有设备,device_model和device_type表与重复字段counter_set_id.我想从counter_set中选择具有counter_set_id的所有值的字段.我只需要从counter_set_id字段中获取值
现在我有这个查询：
SELECT `dev`.`counter_set_id`,`mod`.`counter_set_id`,`type`.`counter_set_id`
FROM `device` AS `dev`
LEFT JOIN `device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
LEFT JOIN `device_type` AS `type` ON `mod`.`device_type_id` = `type`.`id`
WHERE `dev`.`id` = 4;
这将返回3列,但我需要一列中的所有值
这是我认为的最终变体：
SELECT `dev`.`counter_set_id`
FROM `device` AS `dev` LEFT OUTER JOIN
`device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
WHERE `dev`.`id` = 4 AND
`dev`.`counter_set_id` IS NOT NULL
UNION
SELECT `mod`.`counter_set_id`
FROM `device_model` AS `mod` LEFT OUTER JOIN
`device` AS `dev` ON `mod`.`id` = `dev`.`device_model_id`
WHERE `mod`.`counter_set_id` IS NOT NULL;
最佳答案
根据您提供的样本表和所需的输出,听起来您可能想要一个完整的外部联接.并非所有供应商都实现了这一点,但您可以使用LEFT OUTER连接和UNION到EXCEPTION连接来模拟它,其中表格反转如下：
Select name1,name2,A.link_id
From table1 A Left Outer Join
table2 B on A.link_id = B.link_id
Union
Select name1,link_id
From table2 C Exception Join
table1 D on C.link_id = D.link_id
然后你的输出将是这样的：
NAME1 NAME2 LINK_ID
===== ===== =======
text