How do I run this MySQL JOIN query?

Let's say I have 2 tables.

The first table is a list of personas. A user can have many personas.

mysql> select id, user_id, name from personas_personas;
+----+---------+--------------+
| id | user_id | name         |
+----+---------+--------------+
|  8 |       1 | startup      |
|  9 |       1 | nerd         |
| 10 |       1 | close        |
| 12 |       2 | Nerd         |
| 13 |       2 | Startup      |
| 14 |       2 | Photographer |
+----+---------+--------------+
6 rows in set (0.00 sec)

Now, I have another table called "approvals".

mysql> select id, from_user_id, to_user_id, persona_id  from friends_approvals;
+----+--------------+------------+------------+
| id | from_user_id | to_user_id | persona_id |
+----+--------------+------------+------------+
|  2 |            1 |          2 |          8 |
|  3 |            1 |          2 |          9 |
+----+--------------+------------+------------+
2 rows in set (0.00 sec)

If from_user wants to approve to_user to a persona, then a record is inserted.

I'm trying to do this query...

Given a user, find all its personas. Then, for each persona, determine if it's approved for a certain to_user. If so, return is_approved=1 in the result set. Otherwise, return is_approved=0 in the result set.

So this is where I start:

SELECT *
FROM personas_personas
WHERE user_id = 1
LEFT JOIN friends_approvals ON
...but i don't know where to go from here.

So, the final result set should have all the columns in the personas_personas table, and then also is_approved for each of the results.

Answers


 SELECT 
   pp.*,
   CASE
     WHEN exists (
         SELECT 
          * 
         FROM 
          friends_approvals fa  
         WHERE 
          fa.from_user_id = pp.user_id AND
          fa.persona_id = pp.id AND
          fa.to_user_id = 2
          )
      THEN 1
      ELSE 0
   END as is_approved

 FROM 
   personas_personas pp 
 WHERE 
   pp.user_id=1

Or, depending on your taste:

 SELECT 
   pp.*,
   CASE
     WHEN fa.from_user_id IS NOT NULL
      THEN 1
      ELSE 0
   END as is_approved

 FROM 
   personas_personas pp 
     LEFT OUTER JOIN friends_approvals fa ON
       pp.user_id = fa.from_user_id AND
       pp.id = fa.persona_id AND
       fa.to_user_id = 2
 WHERE 
   pp.user_id=1

If I'm understanding your needs correctly, you can do this:

SELECT personas_personas.*,
       CASE WHEN friends_approvals IS NULL THEN 0 ELSE 1 END AS is_approved
  FROM personas_personas
  LEFT
 OUTER
  JOIN friends_approvals
    ON friends_approvals.from_user_id = ...
   AND friends_approvals.to_user_id = personas_personas.user_id
   AND friends_approvals.persona_id = personas_personas.id
 WHERE personas_personas.user_id = ...
;

That will find every personas_personas record with the specified user_id, together with an indicator of whether that user, in that persona, has been "approved" by a specified from_user_id.

(If that's not what you want, then please clarify!)


Need Your Help

Android: how to make only one item of ListView clickable?

android

I am working on a simple to-do list app, and on the main view I have a list of tasks with a checkmark aside to each, as the image below:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.