Sql Server Query design

Question

I have two tables in Sql Server Table1 and Table2.

• The First Table has PartID, Code, Brand
• The Second Table has ID, PartID, AddCode, AddBrand

The idea is that the first table is main table where Some Article is entered with his original code and Brand.

The Second Table is table where we can store additional Codes and Brands which original Article is related to them

Let say that in First Table We have following Data:

PartId   Code    Brand
 100     15FY     MCD

Second Table Has following data:

ID      PartID     AddCode      AddData
1        100        1888        AddBrand1
2        100        FF0-1       AddBrand2

I want to display data with select like this:

PartId   Code    Brand
 100     15FY     MCD
 100     1888   AddBrand1
 100     FF0-1  AddBrand2

I've tried to use:

Select a.PartID, a.Code, a.Brand,b.AddCode,b.AddData
from table1 a left outer join 
table2 b on a.PartId=b.PartId

but i cant figure out how to do it...

Thank you in advance

Answer 1

This sounds more like union all then join:

select PartId, Code, Brand
from ((select t1.PartId, t1.Code, t1.Brand, 1 as seq
       from table1 t1
      ) union all
      (select t2.PartId, t2.AddCode as Code, t2.AddBrand as brand, 2 as seq
       from t2
      )
     ) x
order by PartId, seq;

Note that this orders the results so all PartIds appear together in the result set, with the row from the first table appearing first.

Answer 2

Use Union of both tables like this Select PartId, Code, Brand from table1 UNION ALL Select PartID, AddCode, addData from table2

Answer 3

SELECT *
FROM (
    SELECT A.PARTID
        ,A.CODE
        ,A.BRAND
    FROM TABLE1 A

    UNION ALL

    SELECT B.PARTID
        ,B.ADDCODE
        ,B.ADDDATA
    FROM TABLE B
    ) RESULT
ORDER BY RESULT.PARTID

Answer 4

Use UNION ALL Statement In SELECT Clause :

SELECT PartId, Code,    Brand
FROM Table1
UNION ALL
SELECT PartID ,AddCode  Code,AddData Brand
FROM Table2