Multiple Joins on the same table

Here are few example queries which shows how to use multiple inner joins with sql.

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

If you want each fruit on it's on line(Record set), then it looks like

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

Here is two equivalent queries where one of them uses join and other doesn't.

SELECT codice, 
other1.value AS value_one, 
other2.value AS value_two
FROM main
LEFT OUTER JOIN other as other1 ON (main.id = other1.id_main AND type = 'type_one')
LEFT OUTER JOIN other as other2 ON (main.id = other2.id_main AND type = 'type_two)

is equivalent to

SELECT codice,
(SELECT value FROM other WHERE id_main = id AND type = 'type_one') AS value_one,
(SELECT value FROM other WHERE id_main = id AND type = 'type_two') AS value_two
FROM codice

Query below will give you a clear idea about how to use mutliple joins in the same query. Hope this will help you to understand the usage.

SELECT u1.displayname AS Player1
, u2.displayname AS Player2
, u3.displayname AS Player3
, u4.displayname AS Player4
, u5.displayname AS Player5
, u6.displayname AS Player6
FROM (
(
(
(
(
DCMLeagues AS L
INNER 
JOIN Users AS u1 
ON u1.userid = L.player1
)
INNER 
JOIN Users AS u2
ON u2.userid = L.player2
)
INNER 
JOIN Users AS u3 
ON u3.userid = L.player3
)
INNER 
JOIN Users AS u4 
ON u4.userid = L.player4
)
INNER 
JOIN Users AS u5 
ON u5.userid = L.player5
)
INNER 
JOIN Users as u6 
ON u6.userid = L.player6