please help with SQl query involving 3 tables

Question

Greetings, I have 3 tables in my SQL database as follows

Table Users2

UserID,
EID,
Name,
Email,
Department,
Enabled

Table Sites

SiteID,
SiteCode,
SiteName

Table UserSites2

UsersSitesID,
UserID,
SiteID

What I need to do is, given EID and SiteID, get a full row from the Users2 table AND the SiteID, SiteCode and SiteName from the Sites table WHEN the userID of the retrieved record has an entry in the UserSites2 Table. Here is a sampling of data..

Users2

1, 12345, Me, [email protected], Support, True
2, 12346, you, [email protected], Service, True

Sites

1, 123, Regional HQ
2, 234, National HQ

UserSites2

1, 1, 1
2, 1, 2
3, 2, 2

So given EID 12345 and Site ID 2 I should get the result

1, 12345, Me, [email protected], Support, True, 2, 234, National HQ

and for EID 12346 and SiteID 1 i should get nothing

i know how to join Users2 and Sites to get the full row I want but I don't understand how to make it depend on wether there is an entry in the lookup table for it.

Answer 1

you would do something like this:

select col1,col2,.....from Users, Sites, UserSites 
where Users.eid = 12345 and Sites.siteid = 2 
  and users.user_id = userSites.userid 
  and sites.siteis = usersites.siteid

Answer 2

    select u2.UserID, u2.EID, u2.Name, u2.Email, u2.Department, u2.Enabled, 
           s.SiteID, s.SiteCode, s.SiteName
    from users2 u2
        left outer join usersites us on u2.UserID = us.UserID 
        left outer join sites s on s.SiteID = us.SiteID 
    where u2.EID = 12345  and us.SiteID = 2

I have tested this. It will give you no records if it is not mapped in UserSites. So for EID 12346 and SiteID 1 you will get nothing.

Answer 3

I like to specifically list the joins where possible. I believe something like this would work:

select u2.UserID, u2.EID, u2.Name, u2.Email, u2.Department, u2.Enabled,
       s.SiteID, s.SiteCode, s.SiteName
from Users2 u2 join UserSites2 us2 on u2.UserID=us2.UserID 
     join sites s on us2.SiteID=s.SideID
where u2.EID=<eid> and 
      us2.SiteID=<siteid>

Answer 4

i know how to join Users2 and Sites to get the full row I want, but I don't understand how to make it depend on wether there is an entry in the lookup table for it.

Using a LEFT JOIN means there might not be a supporting record, based on the join criteria. Where records are missing, you'll see null in appropriate column. If you want to only return rows based on the criteria, use a [INNER] JOIN. Here's a good link showing the differences between JOINs and it includes UNIONs.

Here's your query:

SELECT u.userid,
       u.eid,
       u.name,
       u.email,
       u.department,
       u.enabled,
       s.siteid,
       s.sitecode,
       s.sitename
  FROM USERS2 u
  JOIN USERSITES2 us ON us.userid = u.userid
  JOIN SITES s ON s.siteid = us.siteid
 WHERE u.eid = ?
   AND us.siteid = ?