Home > Back-end >  Joining Multiple tables based on a unique ID in one table
Joining Multiple tables based on a unique ID in one table

Time:11-30

I am wanting to join multiple tables based on a ID I fetch from the first table.

Here is an example:

Location:

ID LocationID
1 25
2 15
3 33

Serialized Table:

LocationID PoductID SerialNumber
25 4545454 ABC123
25 1231233 CBA321
25 5432121 BCA213

NonSerialized Table:

LocationID PoductID Quantity
25 6786788 3
25 9898989 15
25 8786678 24

I am trying to return a table that looks like this Result Table

ID Location_ID Product_ID Serial_Num Quantity
1 25 4545454 ABC123 NULL
1 25 1231233 CBA321 NULL
1 25 5432121 BCA213 NULL
1 25 6786788 NULL 3
1 25 9898989 NULL 15
1 25 8786678 NULL 24
SELECT
    Location.ID AS ID,
    Location.LocationID AS Location_ID,
    *** NOT SURE how to fetch ProductID From Both Tables *** Product_ID, 
    Serialized.SerialNumber AS Serial_Num,      
    NonSerialized.Quantity AS Quantity,
    FROM Location
    INNER JOIN NonSerialized ON Location.LocationID = NonSerialized.LocationID
    INNER JOIN Serialized ON Location.LocationID = Serialized.LocationID
    WHERE Location.ID = 1 

CodePudding user response:

select loc.Id, loc.locationId, s.ProductId, s.SerialNumber, null as Quantity   
from Location loc
inner join Serialized s on loc.LocationId = s.locationId
where loc.Id = 1
union
select loc.Id, loc.locationId, ns.ProductId, null, ns.Quantity   
from Location loc
inner join nonSerialized ns on loc.LocationId = ns.locationId
where loc.Id = 1

would do it. You need to union data from 2 sets of data.

CodePudding user response:

You need to do a union, to get the rows from both tables:

select loc.ID, loc.LocationID, s.ProductID, s.SerialNumber, 
       null as Quantity
  from Location loc
  join Serialized s on s.ID = loc.ID
union all
select loc.ID, loc.LocationID, ns.ProductID, null as SerialNumber, 
       ns.Quantity
  from Location loc
  join NonSerialized ns on ns.ID = loc.ID
  • Related