CODEWITHSUNDEEP
sql
DewBoy3d
DewBoy3d

Reputation: 163

SQL query involving three tables and checking whether some result has entry in another table

I have 3 tables in my SQL database as follows

Users2

UserID,
EID,
Name,
Email,
Department,
Enabled

Sites

SiteID,
SiteCode,
SiteName

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

Example of expected result:

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 SiteID 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 whether there is an entry in the lookup table for it.

Upvotes: 0

Views: 201

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332591

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 = ?

Upvotes: 0

Rashmi Pandit
Rashmi Pandit

Reputation: 23818

    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.

Upvotes: 1

Graeme Perrow
Graeme Perrow

Reputation: 57248

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>

Upvotes: 0

ennuikiller
ennuikiller

Reputation: 46965

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

Upvotes: 0

Related Questions