CODEWITHSUNDEEP
mysql
Unknown developer
Unknown developer

Reputation: 5930

Special SELECT statement

I have the following MySQL tables:

USERS(uid,name)   USERS_GROUPS(u_id,r_id,flag)       REQUESTS(rid,status)
------------------------------------------------------------------------
1  John             1  96   1                       96    0
2  Peter            1  97   1                       97    0
3  Helen            2  97   0                       98    0 
                    3  97   0                       99    1 
                    2  98   1
                    1  98   0
                    3  98   0

Above, you may see some sample data. USERS to USERS_GROUPS 1:N,REQUESTS to USERS_GROUPS 1:N (u_id, r_id PKs and FKs). Every record in USERS_GROUPS with the same r_id is considered as belonging to the same group.

I want to select those records from USERS_GROUPS which are associated with a certain uid (e.g. 1) and a certain status (e.g. 0) only if the flag equals 1. However, in that case I'd like SELECT to contain all the records of its group. In other words, based on the sample data I want the following result:

1  96   1
1  97   1
2  97   0
3  97   0

Unfortunately, I cannot find the right query. I 'd appreciate your help.

Upvotes: 1

Views: 63

Answers (2)

KM11
KM11

Reputation: 757

SELECT USERS_GROUPS.u_id,USERS_GROUPS.r_id,USERS_GROUPS.flags
FROM USERS_GROUPS
WHERE r_id IN (SELECT USERS_GROUPS.r_id
               FROM USERS_GROUPS, REQUESTS
               WHERE u_id='1' AND r_id=rid AND status='0');

Upvotes: 1

Arth
Arth

Reputation: 13110

You could try the following:

SELECT ug.*
  FROM user_groups ug
  JOIN user_groups ugo
    ON ugo.flag = :flag
   AND ugo.u_id = :u_id
   AND ugo.r_id = ug.r_id
  JOIN requests ro
    ON ro.rid = ugo.r_id
   AND ro.status = :status

This finds the user_groups (ugo) with their requests (ro) that match the conditions and joins all the user_groups from the same request (ug).

See SQLFiddle with the example you provided.

Upvotes: 0

Related Questions