Next step in using Equivalent of a Full Outer Join

[JohnW]

Hello, I am using the 3-phase method of a Full Outer Join in SQL
e.g. inner, union all, left, union all, right

The result is good except... the two collumns that had the "Is Null" applied
may or may not have a value and not in order e.g.
Ref# Item#
789
123 123
456

What I would like is a merged collumn that is sorted e.g.
Result
123
456
789

Any Ideas? Exporting to a spreadsheet and cut/paste is messy for a one off,
not really looking forward to ongoing maintainence.

Thanks in advance

 

[Lord Kelvan]

just as a note you dont need to do an inner join and a left and a
right and you should use union not union all becuase you will get
duplication unless that is what you want.

ie

select table1.*,table2.*
from table1 left join table2
on table1.id = table2.id
union
select table1.*,table2.*
from table1 left join table2
on table1.id = table2.id

that will give you all information in table1 where it = table2 and
also all blank data in table1 and all blank data in table2

problem with that is it seems on becuase that would state a many to
many join in two tables. but i digress.

can you tell us your table structure because there is a much eaiser
way to do that.

something like

select ref as result
from table1
union
select item as result
from table2

then a second query

select result
from theabovequery
order by result;

hope this helps

Regards
Kelvan

 

[Sylvain Lafontaine]

You'll have to put this into a subquery and use the IIF(...) function to
create your new column.

For the ordering, you can use the number of the column containing the
expression or repeat the IIF() expression or create another subquery.

 

[Sylvain Lafontaine]

BTW, I don't understand why you need the Inner Join as one of the two outer
join can cover it anyway.

 

[JohnW]

Thanks for your response, I tried your first example, works the same as my
3-phase effort - nice, less effort. I think you intended to right join on the
second half of the union.

The new SQL is:
SELECT [COMBINED FINAL - 5].*, [Where Is It 3].*
FROM [COMBINED FINAL - 5] LEFT JOIN [Where Is It 3]
ON [COMBINED FINAL - 5].Vendor_Product_No = [Where Is It 3].[Foundary Ref #]
UNION
SELECT [COMBINED FINAL - 5].*, [Where Is It 3].*
FROM [COMBINED FINAL - 5] RIGHT JOIN [Where Is It 3]
ON [COMBINED FINAL - 5].Vendor_Product_No = [Where Is It 3].[Foundary Ref #]

My tables are text only - imported from excel, once I get the routines
correct, I'll link the tables. At the moment I am mobile so I am working with
copies of the active files.

I tried the first half of your second example, the result contained the
whole join id# - good, but I was also after other details

this sql is:
SELECT [Foundary Ref #] as result
FROM [Where Is It 3]
UNION
SELECT [COMBINED FINAL - 5].Vendor_Product_No as result
FROM [COMBINED FINAL - 5]

Thanks for your really useful help so far,
JohnW

 

[Sylvain Lafontaine]

Of course, you don't really need to use an IIF () statement to create your
new column as you can add it directly into your individual queries (the ones
you make before the UNION). However, you still have to use a subquery (or a
View) if you want to order them.

 

[Lord Kelvan]

what are the other details can you post the table structure of the two
tables.

Regards
Kelvan