VLOOKUP like join in SQL

Question

I have been struggling with this for the past few days.

I am converting an Excel spreadsheet into a SSRS Report but the convenience of Excel's Vlookup is stopping me from producing repeatable results.

I am trying to join the first row of a table in a SQL statement.

Head

partnumber
==========
ABC123
XYZ999

Detail

account  customer  mapped
=======  ========  ======
AA01     ABC123    POOL
UU08     ABC123    POOL
BH09     ABC123    POOL
AA01     XYZ999    CAR

I want to get the mapped value for each partnumber in the head table. I don't care that there are multiple account codes for each partnumber in the detail table - use just one of them is good enough.

My result should be

Result

partnumber  mapped
==========  ======
ABC123      POOL
XYZ999      CAR

I have read other Stack Overflow articles relating to this but I am having trouble translating it into my requirement SQL Server: How to Join to first row

A sample of my data is on http://sqlfiddle.com/#!6/32d6d/1

Can someone with more experience with SQL than me please explain what I am doing wrong?

Thank you

Answer 1

If you want to arbitrarily retain the first record matched based on say the account then you can try using ROW_NUMBER:

SELECT partnumber, mapped
FROM
(
    SELECT
        h.partnumber,
        d.mapped,
        ROW_NUMBER() OVER (PARTITON BY h.partnumber ORDER BY d.account) rn
    FROM Head h
    LEFT JOIN Detail d
        ON h.partnumber = d.customer
) t
WHERE t.rn = 1;

But if the value really does not matter, then a simple GROUP BY query might make the most sense:

SELECT
    h.partnumber,
    MAX(d.mapped) AS mapped
FROM Head h
LEFT JOIN Detail d
    ON h.partnumber = d.customer
GROUP BY
    h.partnumber;

Answer 2

If there can only be on, you can use distinct

select distinct partnumber, mapped
from Head
inner join detail
on Head.partnumber = detail.customer

If there are other possible entries that you want to exclude, use a CTE and a row_number()

with CTE as
(
select customer, 
       mapped, 
       row_number() over (partion by customer order by mapped) as rn
from detail
)
select partnumber, mapped
from Head
inner join CTE
on customer = partnumber
and CTE.rn = 1

Answer 3

This should be the easy query for the above scenario

SELECT DISTINST(A.PARTNUMBER),
    B.MAPPED
FROM HEAD AS A
INNER JOIN DETAIL AS B ON A.PARTNUMBER = B.CUSTOMER;