2

I have two table,the names table and the earnings table.

Name_ID Name_desc
1 mark
2 smith
3 becky
4 jimmy

and the earnings table

ID_1 Earnings_1 ID_2 Earnings_2 ID_3 Earnings_3 ID_4 Earnings_4
1 500 2 5454 3 1247 4 7844

You can try it out here:

https://dbfiddle.uk/d3a3rfG0

Question: How to join Name_ID and ID_ to get the earnings. I need a funtion to check every ID column in the earnings table and if it match then get the earnings.

The goal is a table that looks like this:

Name_ID Name_desc earnings
1 mark 500
2 smith 5454
3 becky 1247
4 jimmy 7844
GMB
  • 216,147
  • 25
  • 84
  • 135
Petersselorse
  • 53
  • 6
  • "Who designs a table this way" - clearly someone who is still learning. Be easy with your words, @siggemannen, and try to help without being condescending. – codekaizen Mar 11 '23 at 22:25
  • Ok, good point. Op. You should think about the design. What happens when you need Earnings # 1000. SQL Server tables doesn't support unlimited number of columns. What's wrong with a ID, Earnings table instead – siggemannen Mar 11 '23 at 22:41

5 Answers5

1

I would recommend unpivoting the earnings table first in a lateral join, so we can then easily join it with the names, and aggregate:

select n.name_id, n.name_desc, sum(x.earnings) earnings
from earnings e
cross apply ( values 
    (e.id_1, e.earnings_1),
    (e.id_2, e.earnings_2),
    (e.id_3, e.earnings_3),
    (e.id_4, e.earnings_4)
) as x(id, earnings)
inner join names n on n.name_id = x.id
group by n.name_id, n.name_desc
order by n.name_id

The upside of this method is that it scans the table only once, as opposed to multiple left joins or unions, so it should perform more efficiently and scale better.

Demo on DB Fiddle

name_id name_desc earnings
1 mark 500
2 smith 5454
3 becky 1247
4 jimmy 7844
GMB
  • 216,147
  • 25
  • 84
  • 135
1

What you think about this code. Look very simple:

SELECT Name_ID, Name_desc
  , COALESCE (  ( SELECT Earnings_1 FROM earnings as c WHERE   c.ID_1 = n.Name_ID )
             ,  ( SELECT Earnings_2 FROM earnings as c1 WHERE c1.ID_2 = n.Name_ID )
             ,  ( SELECT Earnings_3 FROM earnings as c2 WHERE c2.ID_3 = n.Name_ID )
             ,  ( SELECT Earnings_4 FROM earnings as c3 WHERE c3.ID_4 = n.Name_ID ) ) as earnings
FROM NAMES as n
Petersselorse
  • 53
  • 6
0
select n.Name_ID, n.Name_desc, 
       sum(coalesce(e1.Earnings_1, 0) +
           coalesce(e2.Earnings_2, 0) +
           coalesce(e3.Earnings_3, 0) +
           coalesce(e4.Earnings_4, 0)
       ) as earnings
from names n
left join earnings e1 on e1.id_1 = n.Name_ID
left join earnings e2 on e2.id_2 = n.Name_ID
left join earnings e3 on e3.id_3 = n.Name_ID
left join earnings e4 on e4.id_4 = n.Name_ID
group by n.Name_ID, n.Name_desc

DBFiddle

juergen d
  • 201,996
  • 37
  • 293
  • 362
0
SELECT *
FROM Earnings
select n.*,e.Earnings_1 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_1

union
select n.*,e.Earnings_2 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_2

union
select n.*,e.Earnings_3 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_3

union
select n.*,e.Earnings_4 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_4
Dale K
  • 25,246
  • 15
  • 42
  • 71
Anushree Tirwadkar
  • 1
  • 1
0

I wrote dynamic code for it. This code doesn't depend your count of id but you must sure that every name_id value exists in earnings table.

declare @columnlist nvarchar(max)=''
declare @table table(name_id int,earnings bigint)
  
SELECT @columnlist=@columnlist+' select ID_'+ cast(name_id  as nvarchar(10))+',Earnings_'+ cast(name_id  as nvarchar(10))+' from Earnings union all'
FROM NAMES



SELECT @ColumnList=Substring(@ColumnList, 1, Len(@ColumnList) - 10)

insert into @table
exec sp_executesql @columnlist

  select t.*,n.name_desc from @table t join names n on t.name_id=n.name_id
natiq rzazade
  • 94
  • 3