How to list all users, and use a sub-query in an outer join

Question

I want to return all users, and if they went to a conference I want to return the conference information. The @ConferenceID will be a parameter.

SELECT 
    U.UserId,
    O.ConferenceName,
    O.LocationName
FROM Users

My outer join will need something like:

SELECT *
FROM Conferences C
    INNER JOIN Locations L ON C.LocationId = L.LocationId
WHERE UserId = ??
     AND C.ConferenceID = @ConferenceID

Is it possible to perform an outer join so that all users are returned, and then optionally display the conference info if they went to one?

I tried this:

   SELECT 
    U.*,
    oj.

FROM Users U

    OUTER JOIN ( 
        SELECT c.ConferenceName, L.LocationName
        FROM Conferences C
            INNER JOIN Locations L ON C.LocationId = L.LocationId
        WHERE C.ConferenceID = @ConferenceID
    ) AS oj.UserID = U.UserID

But I get an error

The multi-part identifier "U.UserId" could not be bound.

DDL:

User
-UserId


Conference
-ConferenceID
-UserID
-LocationId

Locations
-LocationID

@Hackerman the 2nd query is what I am suggesting I need to put into a subquery. — cool breeze, Apr 09 '18 at 19:53
Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Apr 09 '18 at 19:53
Is there some reason you can't simply join `Conferences` to `Users`? You don't tell us the DDL, so we have no idea. — Tab Alleman, Apr 09 '18 at 19:55
I assume that `U.` refers to `Users`, so what does `O.` refer to, and how is a `Users` record related to the `Conferences` / `Locations` tables? — Andreas, Apr 09 '18 at 19:59
Still doesn't show how a user is related to conference/location. What is `oj.UserID`? --- Your question says *"if they went to a conference"*, but in no way have you defined how to know whether a user went to a conference. Where is that information stored? — Andreas, Apr 09 '18 at 20:00
@Andreas Updated Q. Conference.UserID is in the Conferences table. — cool breeze, Apr 09 '18 at 20:04
Side note: That's not DDL. What DDL stands for is Data Definition Language. It means the create table statements that makes the tables. — Zohar Peled, Apr 09 '18 at 20:09

score 3 · Accepted Answer · answered Apr 09 '18 at 20:07

3

Yes, you can use an outer join, in particular a LEFT JOIN, but you need to move the ConferenceID condition to the join clause, and you need to LEFT JOIN both tables.

SELECT U.UserId
     , C.ConferenceName
     , L.LocationName
  FROM Users U
  LEFT JOIN Conferences C ON C.ConferenceID = @ConferenceID
                         AND C.UserID = U.UserId
  LEFT JOIN Locations L ON L.LocationID = C.LocationId

answered Apr 09 '18 at 20:07

Andreas

154,647
11
152
247

But I want to return all users, many users won't be in the conference table. that is why I thought I need an outer join so it returns NULLs. – cool breeze Apr 09 '18 at 20:09
Since there are no conditions on `U`, this will return all records from `Users`. Since there can be only one `Conferences` record with that `ConferenceID` *(we assume IDs are unique)*, the `LEFT JOIN` will return 0 or 1 record. Same for `Locations`. This means that you get one result row for every user, with `NULL` names if user didn't go to the conference. – Andreas Apr 09 '18 at 20:12
This works but actually I realized I have 1 more join table, and it depends on the 1st LEFT JOIN. So when I add that my result set is just a few rows and doesn't return all the users. Do I need a subquery in this case? Basically my joins are used together to filter the row, they can't be used independantly if that makes sense. – cool breeze Apr 09 '18 at 20:16
@coolbreeze Did you remember to make it a `LEFT JOIN` and to put all conditions in the `ON` clause of that join, and not in the `WHERE` clause of the `SELECT` statement? Remember, if the outer join doesn't find a match, the columns from that table are all `NULL`, so if you write conditions in the `WHERE` clause, the `NULL` value will fail to match your condition. – Andreas Apr 09 '18 at 20:25

How to list all users, and use a sub-query in an outer join

1 Answers1