Complex Query Question

J

Joe Williams

Table 1 has a list of standard materials to make a part. (Fro example, a
part takes materials A, B, and C). Table 2 has a list of actual materials
used (for example, B, C, and D)

now, there exists the situation where we sometimes use alternate materials
that are not on the standard list.

Given Table 1 has A, B, and C and table 2 has B, C, and D, how do I create a
query that shows not only what the standard items are but what we actually
used as well? (In this example, the results would be A, B, C and D)
 
M

Marshall Barton

Joe said:
Table 1 has a list of standard materials to make a part. (Fro example, a
part takes materials A, B, and C). Table 2 has a list of actual materials
used (for example, B, C, and D)

now, there exists the situation where we sometimes use alternate materials
that are not on the standard list.

Given Table 1 has A, B, and C and table 2 has B, C, and D, how do I create a
query that shows not only what the standard items are but what we actually
used as well? (In this example, the results would be A, B, C and D)
Click to expand...


Sounds like a full outer join.

SELECT standard.part, standard.material
FROM standard LEFT JOIN actual
ON standard.part = actual.part
WHERE standard.part = [Enter Part]
UNION ALL
SELECT actual.part, actual.material
FROM actual LEFT JOIN standard
ON actual.part = standard.part
WHERE actual.part = [Enter Part]
AND standard.part Is Null
 
Top