CODEWITHSUNDEEP
sqlfirebird
Simon King
Simon King

Reputation: 195

Combining result of two tables using SQL Firebird

I am using Ostendo which is a FirebirdSQL database.

We have Assemblies. Each assembly consists of a header and many AssemblyLines. Each AssemblyLine can consist of 0-many AssemblyLineProperty records (each ALP record is generally a propertydescription and property value. For example an assembly line might have two properties: Drawing No. with value AB-01 and a Value with value 2 I can run a query that shows the Drawing No. and Value Property on two lines, but I want it on one line. I can achieve what I want by having two tables next to each other in Excel, but this in clumsy and will eventually lead to problems which will have a large cost implication to the business.

Table 1 SQL:

SELECT 
ASSEMBLYLINES.ORDERNUMBER, 
ASSEMBLYLINES.LINECODE, 
ASSEMBLYLINES.LINEDESCRIPTION AS "MATERIAL", 
ASSEMBLYLINES.ORDERQTY, 
ASSEMBLYLINEPROPS.PROPERTYVALUE AS "JOB QUANTITY" 

FROM ASSEMBLYLINEPROPS ASSEMBLYLINEPROPS, ASSEMBLYLINES ASSEMBLYLINES

WHERE ASSEMBLYLINEPROPS.HEADERSYSUNIQUEID = ASSEMBLYLINES.SYSUNIQUEID AND ((ASSEMBLYLINES.ORDERNUMBER='16606R01')) AND (PROPERTYNAME = 'Job Quantity')

Table 2:

SELECT 
ASSEMBLYLINEPROPS.PROPERTYVALUE AS "DRAWING NO." 

FROM ASSEMBLYLINEPROPS , ASSEMBLYLINES 

WHERE ASSEMBLYLINEPROPS.HEADERSYSUNIQUEID = ASSEMBLYLINES.SYSUNIQUEID AND  ((ASSEMBLYLINES.ORDERNUMBER='16606R01') AND (ASSEMBLYLINEPROPS.PROPERTYNAME='Drawing No'))

How can I combine the two queries into one result so that the Drawing No. and Qty appear on the same line.

I believe I need to do some sort of nesting, not joining. Any answers, clues or hints are appreciated.

Upvotes: 0

Views: 226

Answers (1)

cha
cha

Reputation: 10411

You need to join the ASSEMBLYLINEPROPS table twice to get the property values for each of the properties. I am using LEFT join to make sure that you still have the orders in case one of the properties is missing:

SELECT 
ASSEMBLYLINES.ORDERNUMBER, 
ASSEMBLYLINES.LINECODE, 
ASSEMBLYLINES.LINEDESCRIPTION AS "MATERIAL", 
ASSEMBLYLINES.ORDERQTY, 
Q.PROPERTYVALUE AS "JOB QUANTITY",
D.PROPERTYVALUE AS "DRAWING NO." 
FROM ASSEMBLYLINES LEFT JOIN ASSEMBLYLINEPROPS Q
ON Q.HEADERSYSUNIQUEID = ASSEMBLYLINES.SYSUNIQUEID AND Q.PROPERTYNAME = 'Job Quantity'
LEFT JOIN ASSEMBLYLINEPROPS D 
ON D.HEADERSYSUNIQUEID = ASSEMBLYLINES.SYSUNIQUEID AND D.PROPERTYNAME = 'Drawing No'
WHERE ASSEMBLYLINES.ORDERNUMBER='16606R01'

Upvotes: 1

Related Questions