Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a application and have this table for users relationships. I need to build some queries like for example:

  • Who invited me as a friend?
  • Who I invited to be my friend?
  • Who I accept as friend?

And need some help because for me are complex queries :(. Also I don't know if open trhead for each of the question or leave all them here in this topic, what's the best?

For the first question: I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_app if not exists in id_user_rq as the title said. I try this query:

SELECT DISTINCT A.*, p.*, u.email FROM default_relations_users A 
    LEFT JOIN default_relations_users B ON (A.id_user_rq = B.id_user_ap
    AND A.id_user_ap = B.id_user_rq AND A.id_user_rq = 1)
    LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
    LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
    GROUP BY A.id_user_rq

But it's not working, any advice or help?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted
SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_ap IS NULL
              GROUP BY A.id_user_rq

I changed A and B to be subqueries using 1 for id_user_rq and id_user_ap

If you want the opposite, just reverse A and B subqueries to something like this:

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq) 
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_rq IS NULL
              GROUP BY A.id_user_rq
share|improve this answer
    
Nop, doesn't work. Your last query returns id_user_rq=2 and isn't correct because exists at id_user_ap –  ReynierPM Jun 5 '12 at 17:55
    
"WHERE B.id_user_ap IS NULL" why this? I can't understand why you filter by B.id_user_ap IS NULL because this will never happen. –  ReynierPM Jun 5 '12 at 18:02
    
I suggested WHERE IS NULL because you have LEFT JOINs. The query behavior may change if you use INNER JOINs instead. Then, there is no need for WHERE IS NULL. –  RolandoMySQLDBA Jun 5 '12 at 19:09
    
I tried with this query: SELECT DISTINCT A . * , p . * , u.email FROM default_relations_users A INNER JOIN default_relations_users B ON ( A.id_user_rq = B.id_user_ap AND A.id_user_ap = B.id_user_rq AND A.id_user_rq =1 ) INNER JOIN default_users u ON ( u.id = A.id_user_rq ) INNER JOIN default_profiles p ON ( p.user_id = A.id_user_rq ) GROUP BY A.id_user_rq LIMIT 0 , 100 but this doesn't return any values. I'm wrong? –  ReynierPM Jun 5 '12 at 19:40
    
When you used LEFT JOIN, did any data come back ? If so, how many rows? –  RolandoMySQLDBA Jun 5 '12 at 19:43
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.