我有以下查询
SELECT * FROM (
(SELECT value FROM table_a WHERE link_id = (SELECT id FROM table_b WHERE col_id=1 and somefield='some_text')) as item1,
(SELECT value FROM table_a WHERE link_id = (SELECT id FROM table_c WHERE col_id=7 and somefield='some_other_text')) as item2,
(SELECT value FROM table_a WHERE link_id = (SELECT id FROM table_m WHERE col_id=121 and somefield='more_text')) as item3
)
如果所有内部选择都返回了一些东西,那么一切都正常,但是如果其中一个内部选择没有返回一些东西,则整个查询不会返回任何东西。
有什么可以添加到查询中的吗?这样,如果其中一个内部选择没有返回,那么就会在其位置返回一个空字符串?
因此,例如,第一个查询可能返回值="返回值a",第二个查询可能会返回"值b",然后第三个查询会返回",即由于找不到行,因此为空字符串。然后,这将返回一个包含3列的结果集-
'returning value a' ,'value b',''
这个查询的全部意义在于,我想要一个从多个different表返回值的结果集。这些内部选择是基于参数从php动态创建的,因此它们将是任意数量的不同选项。
若查询可以写得更好,我很乐意更改它,只要我得到一个包含每个内部选择的值的结果集,或者如果选择不返回任何,则得到一个空字符串
你试过这样的东西吗:
SELECT `table_b`.* FROM `table_b`
LEFT JOIN `table_a` ON (`table_b`.`id`=`table_a`.`link_id`)
WHERE `table_a`.`link_id` IN (1,2,3);
对于LEFT JOIN
,table_b将返回具有null
值的字段。
好吧,您并没有通过提供详细的查询来帮助我们,但根据您的代码,这是我可以想到的:
SELECT DISTINCT ta1.colName item1, ta2.colName item2, ta3.colName item3
FROM table_a ta1
LEFT JOIN table_a ta2
ON ta2.link_id = (
SELECT id
FROM table_c
WHERE col_id = 7
AND somefield = 'some_other_text'
)
LEFT JOIN table_a ta3
ON ta3.link_id = (
SELECT id
FROM table_m
WHERE col_id = 121
AND somefield = 'more_text'
)
WHERE ta1.link_id = (
SELECT id
FROM table_b
WHERE col_id = 1
AND somefield = 'some_text'
)
如果您愿意的话,您只能使用LEFT JOIN
s来执行此操作,但我在WHERE
子句中添加了它,以避免浪费表查找。
问我你不明白的事。
好吧,也许这不是最优雅的解决方案,但它应该完成任务:
SELECT
item1.value,
item2.value,
item3.value
FROM
(
(SELECT value FROM table_a WHERE link_id = (SELECT id FROM table_b WHERE col_id=1 and somefield='some_text')) as item1
RIGHT JOIN (SELECT NULL) t1 ON TRUE
),
(
(SELECT value FROM table_a WHERE link_id = (SELECT id FROM table_c WHERE col_id=7 and somefield='some_other_text')) as item2
RIGHT JOIN (SELECT NULL) t2 ON TRUE
),
(
(SELECT id FROM table_m WHERE col_id=121 and somefield='more_text')) as item3
RIGHT JOIN (SELECT NULL) t3 ON TRUE
)