How to get data from one table based on a column from another table?-CodePudding

I have two tables, sample data below

 tblID (ID, Quantity, Project, Region, Date)
 tblAltID(ID, AltID)

tblID

ID	Quantity	Project	Region	Date
123	1	1	US	08-09-2022

tblAltID

ID	AltID
123	[456,789]

I want to be able to search for a specific 'ID' and have it bring up the data for the 'AltID', this is what I have so far (The alternate IDs in this table are formatted like "[123, 456, 789]")

    Select a.ID, b.AltID, a.Quantity, a.Project, a.Region, a.Date

    From tblID as a

    Inner Join (Select ID, AltID From tblAltID CROSS JOIN UNNEST(AltID)) as b on a.ID 
    = b.ID

    Where ID = '123'
    and Date = (Select max(Date) from tblID

    Order By Quantity Desc

For example the ID is '123', its alternates are '456' and '789', I need to obtain the quantity, project, region, and date for 123, 456, and 789. Should look something like this ->

ID	AltID	Quantity	Project	Region	Date
123	null	1	3	US	08-09-2022
123	456	1	1	US	08-09-2022
123	789	2	2	Europe	08-09-2022

For reference, all of the alternate IDs also exist in the ID table. So, I could search for ID = 456 and find the quantity, project, region, and date that way. In this example, 123, and 789 would be the alternates.

CodePudding user response：

Select a.ID, b.AltID, a.Quantity, a.Project, a.Region, a.Date
From tblID as a

JOIN tblAltId as b ON b.AltId = a.ID
WHERE a.ID = '123'
AND Date = (Select max(Date) from tblID
Order By Quantity Desc

CodePudding user response：

You Could use simple union code if you need only search one time in your tblAltID. You need to show all records in the tblID where tblID.ID=123 and also show all records related to the tblAltID where tblAltID.ID = 123 as the following code sample:

Select t1.ID, t1.Quantity, t1.Project, t1.Region, t1.Date 
From tblID t1
where t1.ID = 123

union 

Select t1.ID, t1.Quantity, t1.Project, t1.Region, t1.Date
From  #tblID t1
inner join (Select ID, AltID From tblAltID CROSS JOIN UNNEST(AltID)) t2 
on t1.ID = t2.AltID
where t2.ID = 123

Or you can use the following cte code too:

;with cte (ID, Quantity,Project,Region,Date, ALtID) As (
    Select t1.ID, t1.Quantity, t1.Project, t1.Region, t1.Date, t2.AltID
    From  tblID t1
    left join (Select ID, AltID From tblAltID CROSS JOIN UNNEST(AltID)) 
    t2  on t1.ID = t2.ID
    where t1.ID = 123
    
    union All
    select t1.ID, t1.Quantity, t1.Project, t1.Region, t1.Date,null as 
    AltID
    from  tblID t1
    inner join cte c on t1.ID = c.AltID 
    )
    
    Select * 
    from cte t