CODEWITHSUNDEEP
sqlsql-server-2005join
PedroC88
PedroC88

Reputation: 3829

Query for joining all info from three tables

I need to Join 3 tables in SQL Server. Those 3 tables have basically this schema:

   Users                Items                     UsersItems
+--------+    +--------+-------------+    +--------+--------+-------+
| UserID |    | ItemID | Description |    | UserID | ItemId | Value |
+--------+    +--------+-------------+    +--------+--------+-------+
|   1    |    |   1    |    desc1    |    |   1    |    1   |   1   |
|   2    |    |   2    |    desc2    |    |   1    |    2   |   2   |
|  ...   |    |  ...   |    desc3    |    |   2    |    2   |   1   |
|   n    |    |   n    |    desc4    |    |   n    |    1   |   1   |
+--------+    +--------+-------------+    +--------+--------+-------+

As you can see both Users and Items can grow indefinitely and UsersItems is used to express the relation between those two, also including a Value column.

I need a query to retrieve all users, and for each user I need all the items with it's corresponding Value.

If the relation doesn't exist in UsersItems then Null (or a default value) should be returned for that row's Value column.

The expected query result should be:

             ResultSet
    +--------+--------+-------+
    | UserID | ItemID | Value |
    +--------+--------+-------+
    |   1    |   1    |   1   |
    |   1    |   2    |   2   |
    |   1    |   n    |  NULL |
    |   2    |   1    |  NULL |
    |   2    |   2    |   1   |
    |   2    |   n    |  NULL |
    |   n    |   1    |   1   |
    |   n    |   n    |  NULL |
    +--------+--------+-------+

Upvotes: 1

Views: 116

Answers (3)

paulbailey
paulbailey

Reputation: 5346

Okay, since there are several answers that I think aren't correct, I'll post what I think the answer is:

SELECT Users.UserID,
       Items.Description,
       UsersItems.Value
FROM
       Users
CROSS JOIN
       Items
LEFT JOIN
       UsersItems
ON
       Users.UserID = UsersItems.UserID
AND
       Items.ItemID = UsersItems.ItemID

I'm inferring from your comment about nulls that you want to see all Items againsts all Users, with the Value from the UsersItems table where it exists.

Upvotes: 2

aF.
aF.

Reputation: 66687

You do it like this:

SELECT Users.UserID, Items.Description, Items.Value
FROM Users
LEFT OUTER JOIN UsersItems ON Users.UserID = UsersItems.UserID
LEFT OUTER JOIN Items ON UserItems.ItemID = Items.ItemID

For more info on left outer join read the following:

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the right table returns one row and the left table returns more than one matching row for it, the values in the right table will be repeated for each distinct row on the left table. From Oracle 9i onwards the LEFT OUTER JOIN statement can be used as well as (+).

Upvotes: 0

Maess
Maess

Reputation: 4146

SELECT
     Users.UserID,
     Items.Description,
     Items.Value

FROM Users LEFT OUTER JOIN UsersItems
          ON Users.UserID = UsersItems.UserID
     LEFT OUTER JOIN Items
          ON UserItems.ItemID = Items.ItemID

Upvotes: 1

Related Questions