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