SQL - Issue Attempting Full Outter Join

[SamScib]

I am working with multiple tables (6 at this point, but could be more in the future). Each table represents a separate facility and has a list of part numbers and countries of origin (COO). Each table has a different number of parts and some parts are on some tables but not others. What I would like to do is illustrated simply below, but with multiple tables:

Table 1

Part COO
1A1 US
1A2 MX
1B1 MX
1B2 MX
1C1 US
1C2 US
1C3 MX
1C4 MX

Table 2
Part COO
1A1 US
1A3 US
1B1 MX
1B3 US
1C1 MX
1C2 US
1C4 MX
1C5 MX

Merge the two tables with all possibilities of parts, leaving the parts' COO blank where it doesn't exist on Table 1 or 2:

Table 3
Part T1_COO T2_COO
1A1 US US
1A2 MX
1A3 US
1B1 MX MX
1B2 MX
1B3 US
1C1 US MX
1C2 US US
1C3 MX
1C4 MX MX
1C5 MX

I have tried this using multiple queries and combinations of left joins and outer joins and unions, but the whole thing seems very manual and I'm still having issues.

Is there a way to complete this type of merge with multiple tables all in one query in SQL?

Any help is much appreciated..

Thanks,
Sam

 

[lameid]

In Access there is no support for a Full Outer join, you can use SQL Server instead.

Typically you can accomplish this with two left joins and a Union... but with 6 tables, this is at best cumbersome...

I'd instead try to normalize the data, Union the 6 tables together and add to each select a column for the source. Then you have a list of parts and tables. You could even take that output and take it into a crosstab if you really want that layout but my gut says for performance reasons you would want to make a table out of the Union query.

My hunch is also that this is one of those situations where perhaps the why you want to do something may yield a better solution than trying to target a solution.

 

[PHV]

One way is to create a query that list all the parts:
SELECT Part FROM [Table 1}
UNION SELECT Part FROM [Table 2}
...
UNION SELECT Part FROM [Table 6}

And then you may LEFT JOIN your 6 tables to this query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

That's what I was going to do PHV. Beat me to it.

I was going to normalize into one table first with some "select into" statements, creating a facilityid, and then do something like:

select a.partnum, b.coo as facility1, c.coo as facility2, etc
from
(select distinct partid as partnum
from table1) a
left join table1 b on a.partid=b.partid
left join table1 c on a.partid=c.partid
etc
where
b.facilityid=1 and
c.facilityid=2 and
etc

Took me a minute to realize that the first column in OP's Table 3 is simply the set of distinct part numbers. Once I had "select distinct partid" as the base for that then it becomes left join city from there.

 

[SamScib]

Thanks to all of you. This is very helpful. However, this may seem obvious but I'm having issues with the syntax on multiple left joins... I have ended up with 34 tables (!!) actually, and when trying to join each I'm getting the error, "Syntax error (missing operator) in query expression..... Here is the actual code..

select a.[material #], b.origin, c.origin, d.origin, e.origin, f.origin, g.origin, h.origin, i.origin, j.origin, k.origin, l.origin, m.origin, n.origin, o.origin, p.origin, q.origin, r.origin, s.origin, t.origin, u.origin, v.origin, w.origin, x.origin, y.origin, z.origin, aa.origin, ab.origin, ac.origin, ad.origin, ae.origin, af.origin, ag.origin, ah.origin from
(SELECT distinct [material #] from [global zmev]) a
left join 1200 as b on a.[material #] = b.[material #]
left join 1201 as c on a.[material #] = c.[material #]
left join 1204 as d on a.[material #] = d.[material #]
left join 1208 as e on a.[material #] = e.[material #]
left join 1221 as f on a.[material #] = f.[material #]
left join 1222 as g on a.[material #] = g.[material #]
left join 1223 as h on a.[material #] = h.[material #]
left join 1224 as i on a.[material #] = i.[material #]
left join 1225 as j on a.[material #] = j.[material #]
left join 1226 as k on a.[material #] = k.[material #]
left join 1228 as l on a.[material #] = l.[material #]
left join 1229 as m on a.[material #] = m.[material #]
left join 1239 as n on a.[material #] = n.[material #]
left join 1241 as o on a.[material #] = o.[material #]
left join 1247 as p on a.[material #] = p.[material #]
left join 2010 as q on a.[material #] = q.[material #]
left join 2012 as r on a.[material #] = r.[material #]
left join 3013 as s on a.[material #] = s.[material #]
left join 4049 as t on a.[material #] = t.[material #]
left join 4062 as u on a.[material #] = u.[material #]
left join 4063 as v on a.[material #] = v.[material #]
left join 4064 as w on a.[material #] = w.[material #]
left join 4213 as x on a.[material #] = x.[material #]
left join 4217 as y on a.[material #] = y.[material #]
left join 4219 as z on a.[material #] = z.[material #]
left join 5022 as aa on a.[material #] = aa.[material #]
left join 5023 as ab on a.[material #] = ab.[material #]
left join 5069 as ac on a.[material #] = ac.[material #]
left join 5216 as ad on a.[material #] = ad.[material #]
left join 5258 as ae on a.[material #] = ae.[material #]
left join 5262 as af on a.[material #] = af.[material #]
left join 6001 as ag on a.[material #] = ag.[material #]
left join 8002 as ah on a.[material #] = ah.[material #]
;

Thanks again!!

 

[lameid]

Only thing that looks suspect to me is your table names that are numeric... Try putting square brackets around them...

1200 becomes [1200]

I'm not sure that is your issue... I've also had mixed results personally with subqueries that weren't separate query objects. So maybe there is a nuance there I'm missing. In any case the square brackets is a good start and if I'm wrong, PHV will likely spot it when she sees it.