join Problem

From these 2 tables:

tblOrder tblAdditionalSerialNumber

OrderID SerialNo OrderID SerialNo

101 11NP3 101 11NP4

102 33BM8 101 11NP8

103 VX897 102 34BM9

102 35BM7

102 35BM3

I need to Return:

OrderID SerialNo

101 11NP3

101 11NP4

101 11NP8

102 33BM8

102 39BM9

102 35BM7

102 35BM3

103 VX897

SELECT OrderID, SerialNO

FROM tblOrder

UNION ALL

SELECT OrderID, SerialNO

FROM tblAdditionalSerialNumber

|||Is there a way to do it without using UNION? That would require that a repeat a lot of code.

|||

Jay,

UNION is the 'standard' method to combine data from two tables.

However, this example code might give you an idea.

Code Snippet

SET NOCOUNT ON

DECLARE @.Order table
( OrderID int,
SerialNo varchar(20)
)

DECLARE @.AdditionalSerialNumber table
( OrderID int,
SerialNo varchar(20)
)

INSERT INTO @.Order VALUES ( 101, '11NP3' )
INSERT INTO @.Order VALUES ( 102, '33BM8' )
INSERT INTO @.Order VALUES ( 103, 'VX897' )

INSERT INTO @.AdditionalSerialNumber VALUES ( 101, '11NP4' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 101, '11NP8' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '34BM9' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '35BM7' )
INSERT INTO @.AdditionalSerialNumber VALUES ( 102, '35BM3' )

SELECT DISTINCT
OrderID = coalesce( o.OrderID, a.OrderID ),
SerialNo = coalesce( o.SerialNo, a.SerialNo )
FROM @.Order o
FULL JOIN @.AdditionalSerialNumber a
ON 1 <> 1
ORDER BY OrderID

OrderID SerialNo
-- --
101 11NP3
101 11NP4
101 11NP8
102 33BM8
102 34BM9
102 35BM3
102 35BM7
103 VX897

As a footnote, using 'tbl' as a table prefix is quite 'out of date' since context will always tell you the object is a table. consider that you are wasting keystrokes that provide no value.

|||

Jay,

your example given is a good example of when you have something split into two tables, when it should be just a single table. In such cases, when you want to treat the two as one, 'UNION' is the tool to use.

/Kenneth

|||

You have to use the UNION or UNION ALL to perform this. Arnie sample quire is tricky alternative; but it is really work perfetly with 2 tables..

|||

I always knew I could do it with UNION ALL. I wanted to see if I could avoid repeating the entire query which is much more complicated than the simple example. Anyhow, I decided to go ahead and do it that way.

Thanks guys. Happy coding.