CODEWITHSUNDEEP
sqlt-sqljoin
Ignacio Soler Garcia
Ignacio Soler Garcia

Reputation: 21855

How to make this join with a TSQL query?

I have a table called USERS that has a foreign key to the table GROUPS (a user can pertain to one or none GROUPS). The table USERS also contains a column ISDELETED (a char column with T or F).

I need a query to retrieve all the GROUPS and all the USERS that are not deleted, if all the users in a GROUP are deleted or no users are defined I need the query to return NULL for that GROUP.

I tried with the following query:

SELECT     GROUPS.*, USERS.*
FROM       GROUPS INNER JOIN
                  USERS ON GROUPS.ID = USERS.GROUPID
WHERE USERS.ISDELETED = 'F'

But this query does not returns the groups that are empty. SQL and me are not the best friends in world, some help will be great, thanks.

Upvotes: 0

Views: 93

Answers (3)

Mithrandir
Mithrandir

Reputation: 25337

If you want all the groups, regardless of a match in the users table, you should use a left outer join:

SELECT     GROUPS.*, USERS.*
FROM       GROUPS 
           LEFT OUTER JOIN
           USERS 
           ON GROUPS.ID = USERS.GROUPID AND USERS.ISDELETED = 'F'

Upvotes: 2

Kev Ritchie
Kev Ritchie

Reputation: 1647

You need to use the LEFT OUTER JOIN operator instead of the INNER JOIN.

Upvotes: 0

rosscj2533
rosscj2533

Reputation: 9323

You should just need to do a left outer join - 

SELECT     GROUPS.*, USERS.*
FROM       GROUPS LEFT OUTER JOIN
                  USERS ON GROUPS.ID = USERS.GROUPID
WHERE USERS.ISDELETED = 'F'

Here's a reference I like to use to remind myself of the differences in sql joins.

Upvotes: 1

Related Questions