SQL UNION ALL with a INNER JOIN
- by kOhm
I'm looking for the best way to display all rows from two tables while joining first by one field (dwg) then where applicable a 2nd join on part.
Table1 data consists of schematics(dwg) along with a list of parts required to build the item depicted in the drawing. Table2 consists of data about the actual parts ordered to build the schematic. Some parts in table2 are a combination of parts in table1 (ex: foo and bar in table1 were ordered as foobar in table2).
I can display all rows in both tables with UNION ALL, but this doesn't join on both the dwg and part fields. I looked at FULL OUTER JOIN also, but I haven't figured out how to join first by dwg, then by part. Here is an example of the data.
table1 table2
dwg part qty order dwg part qty
----- ----- ----- ----- ----- ----- -----
123 foo 1 ord1 123 foobar 1
123 bar 1 ord1 123 bracket 2
123 widget 2 ord2 123 screw 4
123 bracket 4 ord2 123 nut 4
456 foo 1 ord2 123 widget 2
ord2 123 bracket 2
ord3 456 foo 1
Desired output:
The goal is to create a view that provides visibility to all parts in table1 and the associated orders in table2 (including those parts that appear in one but not the other table) so that I can see all the drawing parts in table1 and the associated records in table2 along with records in table2 where the part wasn't in table1.
part_request_order_report
dwg part qty order part qty
----- ----- ----- ------ ----- -----
123 foo 1
123 bar 1
123 widget 2 ord2 widget 2
123 bracket 4 ord1 bracket 2
123 bracket 4 ord2 bracket 2
123 ord1 foobar 1
123 ord1 screw 4
123 ord1 nut 4
456 foo 1 ord3 foo 1
Is this possible? Or am I better off iterating through the data to build the report table? Thanks in advance.
