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,

T1:
UserID
UserName
Fruit1
Fruit2
Fruit3

T2:
Fruit_ID
FruitName
FruitCost

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 FROM T1 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
FROM T1
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 FROM T1 JOIN T2 ON Fruit1 = Fruit_ID UNION SELECT UserID, Fruit2 AS FruitID, FruitName, FruitCost FROM T1 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:

Users UserID (PK) UserName Fruits FruitID (PK) FruitName FruitCost FruitUser 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.

-graz