SQL's equivalent of Excels VLOOKUP formula??

[chudson007]

I've got a data set on which I'm trying to perform the same as excel's
vlookup formula using SQL, but do not know how. Can anyone help?

If you picture me having two tables Table_A and Table_B and they look
like this;
Table_A
Name Sport
John Football
John Rugby
Steve Cycling
Steve Running

Table_B
Name Age
John 23
Steve 24
Peter 25

How do I extract All from Table_B and one Sport from Table_A

I.E I want to end up with something like this...

Name Age Sport
John 23 Football
Steve 24 Cycling
Peter 25 NULL
Thanks in advance,
Ciarán

[David Portas]

A JOIN between those two tables is what you need. Something like this:

SELECT B.name, A.age, B.sport
FROM Table_B AS B
LEFT JOIN Table_A AS A
ON A.name = B.name

However, your specification is incomplete so the above isn't quite what
you asked for. It isn't obvious why you only showed "Football" for John
and "Cycling" for Steve when each of those names has TWO rows in
Table_A. What is the rule for which row to display? A table is not a
spreadsheet. Tables have no inherent logical order so it isn't good
enough to say you only want to see the "first" row - no such concept
exists unless "first" is defined in your data.

Maybe you just want to show the first sport in alphabetical order, in
which case:

SELECT B.name, A.age, MIN(B.sport)
FROM Table_B AS B
LEFT JOIN Table_A AS A
ON A.name = B.name
GROUP BY B.name, A.age

--
David Portas
SQL Server MVP
--

[chudson007]

David,

Thanks for your response.
I do not have a rule for which row to display; I want only one record
for each person and any sport associated with that person.

Regards,
Ciarán