Multiple Joins to the same Table in a Query

By Bill Graziano on 30 November 2000 | Tags: SELECT

Samuel writes "I wanna create a SQL query that will take each fruit1, fruit2, and fruit3 from T1 and match it with it's corresponding record in T2, and make a record set of the information . . ." Read on for the rest of Samuel's question and some interesting tricks you can do with a table alias.

The rest of his question reads "I have a similiar database like this,



so in the end i will have a record set that will only contain information on the 3 fruits that is in the users record in T1."

I'm actually going to give you three different answers to this question. The first two answers are about your question and the last is how I think you might redesign your database. Joining for the first fruit is pretty easy:

SELECT Fruit1, FruitName, FruitCost
JOIN T2 ON Fruit1 = Fruit_ID

The code looks pretty clean because you named you Foreign Keys (Fruit1, Fruit2 and Fruit3) different than your Primary Keys (Fruit_ID). Joining for the second fruit is a little more difficult. You have to join T1 to T2 twice. You can use table aliases to refer to a table twice. That code looks like this:

SELECT Fruit1, F1FruitName = F1.FruitName, F1FruitCost = F1.FruitCost,
Fruit2, F2FruitName = F2.FruitName, F2FruitCost = F2.FruitCost
JOIN T2 F1 ON Fruit1 = F1.Fruit_ID
JOIN T2 F2 ON Fruit2 = F2.Fruit_ID
When we joined to the T2 table we aliased the table to a different name. This can be handy when you have long table names. It also let's you join to two separate instances of the T2 table. You need to use the tables aliases (or table names) in your list of columns so you know which tables each field came from. You'll notice I also renamed each column to make it more obvious.

In your question you said you wanted a "record set". It might be that you want each fruit on it's on line. That would look something like this:

SELECT UserID, Fruit1 AS FruitID, FruitName, FruitCost
JOIN T2 ON Fruit1 = Fruit_ID
SELECT UserID, Fruit2 AS FruitID, FruitName, FruitCost
JOIN T2 ON Fruit2 = Fruit_ID

That will handle the case for two fruits and I'll leave you to add the third fruit. You might also consider designing your database just a little different. Maybe something that looks like this:
UserID (PK)

FruitID (PK)
UserID (FK, PK)
FruitID (FK, PK)

Your existing data model supports three fruits per user. It is also difficult to add that fourth fruit. Although not knowing your requirements, four fruits per user might not be a requirement. This model allows an unlimited number of fruits per user. This design does only allow each user to have a fruit once. You could add a new Primary Key to FruitUser if users were required to have the same fruit multiple times.


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Need help with nested list in SQL (1d)

Can't restore bak file from MS SQL Server 2000 (3d)

Connecting to SQL Server from externally with proxy server (3d)

How to Find if Java is Installed (4d)

How To Insert Title At Start of SQL Results and Between Another SQL Result Sets (4d)

Help with sql query find combinations that are not equal, please (4d)

Transpose Query Help (5d)

Excel blobs and BCP extract commands (6d)

- Advertisement -