CODEWITHSUNDEEP
sqlsql-server
Bob Stout
Bob Stout

Reputation: 1259

querying data between three tables

Just wanted confirmation to make sure I'm doing this right before I try it. Kinda weak on joins.

I have three tables:

USER                     PERSONGROUPTOPERRSON            PERSONGROUPS
    id                     userId                          groupID
    firstName              groupId                         groupName
    lastName          
    lastUpdated

I need all users who belong to groups with "volunteer" in the group name.

Would this query be correct?

SELECT
    a.firstName, a.lastName, a.lastUpdated
FROM
    user as a, persongrouptoperson as b
INNER JOIN ON
    a.id = b.userId
WHERE
    b.groupId
IN 
    (SELECT
        groupID
     FROM
        persongroups
     WHERE
        groupName like '%volunteer%'
     );

Upvotes: 2

Views: 67

Answers (3)

kjmerf
kjmerf

Reputation: 4335

Not exactly. Try it like this:

SELECT user.firstname, user.lastname, user.lastupdated
FROM user
INNER JOIN persongrouptoperson ON user.id = persongrouptoperson.userid
INNER JOIN persongroups ON persongrouptoperson.groupid = persongroups.groupid
WHERE groupName like '%volunteer%'

The idea is to join all three tables together. You can do this pretty simply with inner joins. You just have to link the common keys. So for instance, the user id field in the user table matches the userid field in the person group to person table. Then the group id field in the person groups table matches the group id field in the person group to person table. Link them with ON statements.

Once the tables are linked, you can imagine that each row contains the user information and the group information. So your where clause can directly call out what you are looking for in that group information.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

You can do this with inner joins. I added distinct in case your users can be in multiple groups with the word volunteer in it. I also recommend using more intuitive aliases for tables instead of a, b, c.

select distinct
   u.firstName
 , u.lastName
 , u.lastUpdated
from User as u
  inner join PersonGroupToPerson as pgp on u.id = pgp.userId
  inner join PersonGroup as pg on pgp.groupId = pg.groupId
where pg.GroupName like '%volunteer%'

Upvotes: 1

Pops
Pops

Reputation: 498

SELECT
    a.firstName, a.lastName, a.lastUpdated
FROM
    user as a
INNER JOIN  persongrouptoperson as b ON
    a.id = b.userId
INNER JOIN persongroups as c ON
    b.groupID = c.groupID
WHERE c.groupName like '%volunteer%'

Upvotes: 1

Related Questions