CODEWITHSUNDEEP
sql
zsquare
zsquare

Reputation: 10146

Include count of one-to-many relation in SQL query

I have the following tables:

users
+----------+----------+----------+
| id       | name     | dob      |
+----------+----------+----------+

authentications
+----------+----------+----------+ 
| id       | user_id  | provider |
+----------+----------+----------+

A user, has-many authentications. user_id in the authentications table is a foreign key to the users table.

Im trying to comple the following output:

+---------------+---------------+---------------+---------------+ 
|    user.id    |   user.name   |   user.dob    |       C       |
+---------------+---------------+---------------+---------------+

Here, C is, the count of the users authentications where provider is 'facebook'. If instead of the count, I could get a boolean value, ie true if authentications are present, false if not, that would be even better.

I cant figure how to go about constructing the query. Any pointers would be much appreciated.

Upvotes: 0

Views: 2232

Answers (4)

Syd
Syd

Reputation: 16

1) This will return the actual count of Authorization of type FB for every user :

`

Select Name UserName, ID UserID, COUNT(FBAuth) FBAuthCount
from Users LEFT JOIN
(Select UserID, COUNT(AuthType) FBAuth from Auth 
Where AuthType = 'FB'
Group by USERID) Auths on (Users.ID = Auths.UserId)
Group by ID, Name

`

2) This will return the flag true or false for every user depending on thier authorization type:

`

Select Name UserName, ID UserID, 
CASE WHEN COUNT(FBAuth) > 0 THEN 'TRUE' ELSE 'FALSE' END FBAuthFLAG
from Users LEFT JOIN 
(Select UserID, COUNT(AuthType) FBAuth from Auth 
Where AuthType = 'FB'
Group by USERID) Auths on (Users.ID = Auths.UserId)
Group by ID, Name

`

Using a join on smaller dataset: faster response. Finally, I believe this is the one:

`

SELECT users.id,
       users.email,
       users.name,
       users.sid,
       users.created_at,
       ( CASE
           WHEN fb_auth > 0
           THEN 'true'
           ELSE 'false'
         END ) AS facebook
FROM   users
       LEFT JOIN (SELECT authentications.user_id,
                         Count(authentications.provider) fb_auth
                  FROM   authentications
                  WHERE  provider = 'facebook'
                  GROUP  BY user_id) auths
              ON ( users.id = auths.user_id )
GROUP  BY users.id,
          users.email,
          users.name,
          users.sid,
          users.created_at

`

Upvotes: 0

Lori Lalonde - MSFT
Lori Lalonde - MSFT

Reputation: 368

Create your query with your JOIN between users and authentications, and add in a GROUP BY on the User Id field first. Your group by will need to contain subsequent fields that you wish to list in your results although they won't change the effect of the results:

SELECT u.id, u.name, u.dob, COUNT(a.user_id) as authCount
FROM users u 
LEFT JOIN authentications a on u.id = a.user_id
WHERE a.provider = 'facebook'
GROUP BY u.id, u.name, u.dob

Upvotes: 2

zsquare
zsquare

Reputation: 10146

The earlier answers worked, but were extremely slow. The query used to take 15-20 minutes, to run. This one, suggested by a friend runs much faster (~4s for the same dataset).

SELECT users.id,
       users.email,
       users.name,
       users.sid,
       users.created_at,
       ( CASE
           WHEN fb_auth > 0
           THEN 'true'
           ELSE 'false'
         END ) AS facebook
FROM   users
       LEFT JOIN (SELECT authentications.user_id,
                         Count(authentications.provider) fb_auth
                  FROM   authentications
                  WHERE  provider = 'facebook'
                  GROUP  BY user_id) auths
              ON ( users.id = auths.user_id )
GROUP  BY users.id,
          users.email,
          users.name,
          users.sid,
          users.created_at;

Upvotes: -1

Matt Smucker
Matt Smucker

Reputation: 5244

C is the count, B will return 1 if there is a facebook provider and 0 if not (boolean). Updated to use LEFT OUTER JOIN so that all users are included.

SELECT 
   users.id,
   users.name,
   users.DOB,
   SUM(CASE WHEN authentications.provider = 'facebook' then 1 else 0 end) as C,
   MAX(CASE WHEN authentications.provider = 'facebook' then 1 else 0 end) as B
FROM users
LEFT OUTER JOIN authentications
   ON users.id = authentications.user_id
GROUP BY users.id, users.name, users.DOB

Upvotes: 0

Related Questions