user3782230 user3782230 - 14 days ago 9
SQL Question

Sql Server Query design

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

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.