如何将具有1对多关系的表合并为1条记录

问题描述

我需要使用并集将两个具有1对多关系的表组合在一起，但没有成功.

I need to combine two tables with 1 to many relationship using union but to no success.

我一直在尝试使用此代码

I've been trying to use this code

select a.equipmentid,
a.codename,
a.name,
a.labelid,
a.ACQUISITIONDATE,
a.description
from TBL_EQUIPMENTMST a where
a.partofid = '57'
union all
select first 1 b.warrantyid, b.startdate, b.enddate from tbl_equipwarranty b
inner join TBL_EQUIPMENTMST c
on b.equipmentid=c.equipmentid
where c.partofid = '57' and b.servicetype='service' order by b.warrantyid desc
union all
select first 1 d.warrantyid, d.startdate, d.enddate from tbl_equipwarranty d
inner join TBL_EQUIPMENTMST e
on d.equipmentid=e.equipmentid
where e.partofid = '57' and d.servicetype='product' order by d.warrantyid desc

任何人都可以帮助我如何在图像中产生预期的输出.我正在使用firebird作为数据库.如果您在mysql中有解决方案，请告诉我，请病态尝试在firebird中找到对应的对象.

can anyone help me how to produce my expected output in my image. I am using firebird as a database. If you have a solution in mysql kindly tell me and ill try to find the counterpart in firebird.

推荐答案

秘密在于使用2个不同的别名两次加入tbl_equipwarranty.一种是服务保修，另一种是产品保修.您可以通过将服务类型指定为联接的一部分来执行此操作.以下使用ANSI连接，因此可能会在firebird和mysql中工作:

The secret is to join on tbl_equipwarranty twice - using 2 different aliases. One for the service warranty and one for the product warranty. You can do this by specifying the servicetype as part of the join. The following uses ANSI joins so will probably work in firebird and mysql:

SELECT
    a.equipmentid,
    a.codename,
    a.name,
    a.labelid,
    a.ACQUISITIONDATE,
    a.description,
    a.partofid,
    w1.warrantyid as serviceidwarranty, 
    w1.startdate, 
    w1.enddate,
    w2.warrantyid as productidwarranty, 
    w2.startdate, 
    w2.enddate
FROM TBL_EQUIPMENTMST a 
INNER JOIN tbl_equipwarranty w1 
    ON w1.equipmentid = a.equipmentid AND w1.servicetype = 'service'
INNER JOIN tbl_equipwarranty w2 
    ON w2.equipmentid = a.equipmentid AND w2.servicetype = 'Product'
WHERE
a.partofid = '57'

这篇关于如何将具有1对多关系的表合并为1条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！