SQL Query: Join (or select) 2 columns from 1 table with 1 column from another table for a view without extra join columns

This is my very first Stackoverflow post, so I apologize if I am not formatting my question correctly. I’m pounding my head against the wall with what I’m sure is a simple problem. I have a table with a bunch of event information, about 10 columns as so:

Table: event_info

date        location_id   lead_user_id   colead_user_id   attendees   start   end   <and a few more...>
------------------------------------------------------------------------------------------------
2020-10-10       1           3             1                  26       2100    2200     .
2020-10-11       3           2             4                  18       0600    0700
2020-10-12       2           5             6                  6        0800    0900

And another table with user information:

Table: users

user_id      user_name     display_name     email     phone     city
----------------------------------------------------------------------
1            Joe S           goofball        ...
2            John T          schmoofball     ...
3            Jack U          aloofball       ...
4            Jim V           poofball        ...
5            Joy W           tootball        ... 
6            George A        boring          ...

I want to create a view that has only a subset of the information, not full table joins. The event table lead_user_id and colead_user_id columns both refer to the user_id column in the users table.

I want to create a view like this:

date         Location      Lead Name      CoLead Name     attendees
---------------------------------------------------------------------
2020-10-10      1           Jack U          Joe S           26
2020-10-11      3           John T          Jim V           18
2020-10-12      2           Joy W           George A        6

I have tried the following and several iterations like it to no avail…

SELECT 
    E.date, E.location, 
    U1.display_name AS Lead Name, 
    U2.display_name AS CoLead Name. 
    E.attendees
FROM
    users U1, event_info E
INNER JOIN 
    event_info E ON U1.user_id = E.lead_user_id
INNER JOIN 
    users U2 ON U2.user_id = E.colead_user_id

And I get the dreaded

You have an error in your SQL Syntax

message. I’m not surprised, as I’ve really only ever used joins on single columns or nested select statements… this two columns pointing to one is throwing me for a loop. Help!

Answer

correct query for this matter

SELECT 
    E.date, E.location, 
    U1.display_name AS Lead Name, 
    (select display_name from users  where user_id=E.colead_user_id) AS CoLead Name, 
    E.attendees
FROM
    event_info E
INNER JOIN 
     users U1 ON U1.user_id = E.lead_user_id

Advertisement

Answer