MySQL Query to determine field relationship

I have a table with 3 fields. principal, associate, status.

How can I determine whether the logged-in user is either a principal or an associate, and with how many people he is associated? I would also like to determine the status of the relationship.

query = "SELECT M.id, M.surname, M.firstname, R.principal_id
         , R.associate_id, R.status 
         FROM tbl_members M, tbl_relationship R WHERE
         -- ---------------------------------------------
         -- to make sure user exists in the members table
         -- ---------------------------------------------
         (R.principal_id = M.id OR R.associate_id = M.id) 
         AND (logged-in-user = R.associate_id OR logged-in-user = R.principal_id)
         AND R.status =1"

开发者_开发问答ERROR:- THIS LISTS times 2 of everybody in the relationship table.

Tables invloved:-

tbl_members (id, surname, firstname)

tbl_relationship (id, associate_id[FK tbl_member id], principal_id[FK tbl_member id])

associate_id | principal_id | status

1 3 1

1 4 1

2 1 0

2 3 0

5 1 1

6 1 1

From the above how many people are associated with logged-in-user(1)?

Try:

SELECT
      m.id, m.surname, m.firstname,
      COUNT(assoc.id) AS relationships_as_associate, 
      COUNT(princ.id) AS relationships_as_principal
FROM tbl_members m
     LEFT JOIN tbl_relationship assoc 
          ON assoc.associate_id = m.id AND assoc.status = 1
     LEFT JOIN tbl_relationship princ 
          ON princ.principal_id = m.id AND princ.status = 1
WHERE m.id = logged-in-user
GROUP BY m.id

(guessing that relationships with status = 0 can be ignored)

If you only want the number of relationships this user is in (no matter the user's role):

SELECT
      m.id, m.surname, m.firstname,
      COUNT(rel.id) AS active_relationships
FROM tbl_members m
     LEFT JOIN tbl_relationship rel
          ON (rel.associate_id = m.id OR rel.principal_id = m.id)
          AND rel.status = 1
WHERE m.id = logged-in-user
GROUP BY m.id

EDIT:

According to your comment, this is a query that will get all the users who are in relationship with the logged in user, with their details, and the role they are playing in the relationship (some of them are principals, others are associates):

SELECT m.id, m.firstname, m.surname, temp.role
FROM tbl_members m
JOIN ((SELECT rel.principal_id as id, 'Principal' as role
       FROM tbl_relationship rel
       WHERE rel.associate_id = logged-in-user
       AND rel.status = 1)
     UNION
      (SELECT rel.associate_id as id, 'Associate' as role
       FROM tbl_relationship rel
       WHERE rel.principal_id = logged-in-user
       AND rel.status = 1)
     ) as temp
ON temp.id = m.id

I'm still not 100% sure how your system is suppose to work, but this is what I came up with.

Select M.id, surname, firstname, NumPrincipal, NumAssociate, 'Type' =
Case 
    When NumPrincipal > 0 Then 'Principal'
    Else 'Associate'
End

from

(Select * From tbl_members) AS M

Left Join

(Select M.id, COUNT(M.id) 'NumPrincipal' from 
tbl_members M inner join tbl_relationship R on M.id = R.principal_id
Group By M.Id) AS P

On M.id = P.id

Left Join

(Select M.id, COUNT(M.id) 'NumAssociate' from 
tbl_members M inner join tbl_relationship R on M.id = R.associate_id
Group By M.Id) AS A

On M.id = A.id

Where M.id = logged_in_user_id

The part that still seems weird to me is if they initiate an relationship then they will be a principal, but they can also be an associate in another case, it depends on a specific relationship between two users. My code is just if a user logs in and they have been principal before then they are a principal. Not sure if you want code that is like the current user is looking at a specific relationship they have with another user, find who is principal in that relationship?

Edit: Heres the code to see if the current user is the principal when looking at a single relationship between the current user and another user. This assumes there is only one relationship between two users or you will get multiple records back.

Select *,
'type' = 
case
    when principal_id = currentUserId then 'Principal' 
    else 'Associate'
end

 from tbl_relationship where
(associate_id = currentUserId  or principal_id = currentUserId ) 
and
(associate_id = OtherUserId or principal_id = OtherUserId )

Edit Again, Here is the number of associates regardless of Principal/Associate:

Select M.id, surname, firstname, NumAssociates 

From

tbl_members M

Left Join

(Select id, count(NumAssociate) 'NumAssociates' From 

((Select M.id, M.id 'NumAssociate' from 
tbl_members M inner join tbl_relationship R on M.id = R.principal_id where status = 1) 

Union All

(Select M.id, M.id 'NumAssociate' from 
tbl_members M inner join tbl_relationship R on M.id = R.associate_id where status = 1)) AS T

Group By Id) AS N

on M.id = N.Id