CODEWITHSUNDEEP
sqldatabasepostgresql
Brijesh pant
Brijesh pant

Reputation: 208

Select entry with only one entry in many to many relationship

Say I have a database table user_provider, that contains many to many relationship of user and provider table.
I want to get only those userId which is having only one provider

 SELECT spr1.SU_URS_ID,count(spr1.SU_URS_ID)
 FROM overstappen.spr_usr spr1 
 WHERE spr1.SU_URS_ID in ( select spr.SU_URS_ID 
                           from overstappen.spr_usr spr 
                           where spr.SU_SPR_ID =40) 
 GROUP BY spr1.SU_URS_ID;

Above query returns all users which is having SU_SPR_ID 40. And the total no of users with same userId in spr_usr table.

I only want to get user with count 1. i.e user with only one provider.

Thanks in advance.

Upvotes: 2

Views: 71

Answers (2)

Horia
Horia

Reputation: 1612

Try adding at the end of your query HAVING COUNT(spr1.SU_URS_ID) = 1:

SELECT  spr1.SU_URS_ID,count(spr1.SU_URS_ID) 
FROM    overstappen.spr_usr spr1 
WHERE   spr1.SU_URS_ID in ( 
            SELECT  spr.SU_URS_ID 
            FROM    overstappen.spr_usr spr 
            WHERE   spr.SU_SPR_ID =40) 
GROUP BY spr1.SU_URS_ID
HAVING COUNT(spr1.SU_URS_ID) = 1;

Upvotes: 1

rcs
rcs

Reputation: 7197

Try this

SELECT   spr1.SU_URS_ID, COUNT(spr1.SU_URS_ID)
FROM     overstappen.spr_usr spr1 
WHERE    spr1.SU_URS_ID IN 
         (SELECT   spr.SU_URS_ID 
         FROM     overstappen.spr_usr spr WHERE spr.SU_SPR_ID =40) 
GROUP BY spr1.SU_URS_ID
HAVING   COUNT(spr1.SU_URS_ID) = 1

Upvotes: 1

Related Questions