您可以使用(id,name)作为加入条件,将两个表中的列组合在一起:
select
a.id as id,
a.name as name,
a.somefield1 || ' ' || b.somefied1 as somefield1
from tablea a, tableb b
where a.id = b.id
and a.name = b.name
and b.name = 'mooseburgers';
如果您只想加入(id)并组合名称和somefield1列:
select
a.id as id,
a.name || ' ' || b.name as name,
a.somefield1 || ' ' || b.somefied1 as somefield1
from tablea a, tableb b
where a.id = b.id
and b.name = 'mooseburgers';
虽然我不得不承认这是一种相当不寻常的做事方式.我假设你有你的理由:-)
如果我误解了你的问题并且你只想要两个表的更传统的联合,那么使用类似的东西:
select id, name, somefield1, '' as somefield2 from tablea where name = 'mooseburgers'
union all
select id, name, somefield1, somefield2 from tableb where name = 'mooseburgers'
这不会合并行,而只会附加两个查询中的行.如果要删除重复行,请单独使用union,但如果您确定没有重复项或者您不希望删除它们,则union all通常更有效.
根据您的编辑,实际查询将是:
select name, somefield1 from tablea where name = 'zoot'
union all
select name, somefield1 from tableb where name = 'zoot'
(或者如果你不想要重复,其中a.name == b.name ==’zoot’和a.somefield1 == b.somefield1).