SQL - Select inside Select

Question

Hello guys.

My skills with SQL are simple, so i'm not sure how to do this.. Here it goes.

Lets consider this.

Table A
ID              Name              LastNameID
1               Jonh              23
2               Helen             19

Table B
ID              LastName
23              Bauer
19              Summers

.

SELECT ID Name LastNameID FROM TableA

How can i add another Select inside that one that will return the LastName from Table B based on the LastNameID in Table A.

In this case i usually use a simple function with the Programming language that i use to do that for each returned Row.

But i think its time to do that with SQL alone.

Any help is appreciated.

Answer 1

You just need to join the tables using the LastNameId field:

SELECT TableA.[Name] AS FirstName, TableB.LastName
FROM TableA 
  INNER JOIN TableB ON TableA.LastNameId = TableB.LastNameId

Answer 2

You are asking "how to do it with a select inside select". Usually, such queries are written in the form of joins, not as sub-selects. Doing joins is better than doing sub-selects for small number of tables.

The query for your application would be

SELECT firstNames.Name, lastNames.LastName FROM TableA firstNames, TableB lastNames
WHERE firstNames.LastNameId = lastNames.LastNameId;

This is the same as writing the query in an explicit join form as shown by Chris in his answer.

Answer 3

There are some good answers here, but if you actually have the first name in one table and last name in another table linked by an ID - I think it is time to review the basics. Start with looking at the Northwind database andmthen some of the database starter kits here.