Query with two tables one with many fields.
Posted on 2011-03-16
I have two tables. The first has the service failure information for an order ,SRVFAIL. it is linked to the table ORDER on ORODR# = SVORD#. This is a one to one relationship and works just fine with an inner join. The problem is when I join the comment table, SRVCOM, The way the table structure is, which I can not change, in SRVCOM there could be multiple comments for each order number and therefore there could be many rows. So when I join the tables I get many rows for each service failure but I only want one row for each service failures and include ALL of the comments. This is an Idea of what I have: SELECT ORODR#,SRVCODE,SRVCOM FROM ORDER INNER JOIN SRVFAIL ON ORODR# = SVORD# INNER JOIN SRVCOM ON SVCORD WHERE ORODR = '118111'.
Oh by the way this is a pass through query so the # are OK as it is DB2.