how to combine tables with 1 to many relationship into 1 line of record

Question

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

enter image description here 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

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.

except for the aliases your 2nd and 3rd queries seem identical? — FuzzyTree, Nov 05 '14 at 04:09
sorry regarding on the identical. I corrected that in my code but its still showing an error. — Mandz, Nov 05 '14 at 04:58

Donal · Accepted Answer · 2014-11-05T04:35:51.360

4

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'

edited Nov 05 '14 at 04:35

answered Nov 05 '14 at 04:23

Donal

31,121
10
63
72

1

You're a life saver. With these knowledge I can now display the report using 1 select statement easily. I was planning on using 3 select and combine it with 1 recordset and display it at the same time as my last resort. Thank you for the new knowledge^_^ – Mandz Nov 05 '14 at 04:39
@Mandz Glad to be of help :-) – Donal Nov 05 '14 at 04:40
I have an another problem. Now I need to combine tables with 1 to N relation ship but now I need only the last value of N. Can you help me? http://stackoverflow.com/questions/26882396/combine-tables-with-1-to-n-relationship-into-1-line-of-record-with-the-last-valu – Mandz Nov 12 '14 at 08:35

score 3 · Answer 2 · answered Nov 05 '14 at 04:37

This will give you required result only when you have unique warrantyid present for an equipmentid in table.

SELECT 
a.equipmentid,
a.codename,
a.name,
a.labelid,
a.ACQUISITIONDATE,
a.description,
a.partofid,
B.warrantyid AS serviceidwarranty,
B.Startdate,
B.Enddate,
C.warrantyid AS productidwarranty,
C.Startdate,
C.Enddate

FROM TBL_EQUIPMENTMST A
LEFT OUTER JOIN tbl_equipwarranty B ON A.equipmentid=B.equipmentid AND B.Servicetype='Service'
LEFT OUTER JOIN tbl_equipwarranty C ON A.equipmentid=C.equipmentid AND C.Servicetype='Product'

I tried you're code sir and it's also working. Sorry if can't choose you're post as an answer since I already chosen my answer a while ago. Thanks anyways. ^_^ — Mandz, Nov 05 '14 at 04:46

how to combine tables with 1 to many relationship into 1 line of record

2 Answers2

Linked