2

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?

1
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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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