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.

This question and answers originated from www.stackoverflow.com
Question by (11/30/2011 5:08:13 AM)

Answer

 
 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
Answer by
Find More Answers
Related Topics  mysql  sql  database  query
Related Questions

  • How would I perform this MySQL Query?

    Suppose I have a table called "approvals". mysql> desc approval; +-------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | …

  • How do I do this query in MySQL? (datetime)

    Suppose I have a datetime column in MySQL. How do I select all that have a datetime within 2500 seconds of the current datetime? SELECT ALL where current_datetime - that_datetime < 2500 ...

  • How do I write this MySQL query?

    I am working on an Asset DB using a lamp stack. In this example consider the following 5 tables: asset, server, laptop, desktop, software All tables have a primary key of id, which is a uni…

  • How do I query "begin with" in MySQL?

    SELECT stuff REGEXP 'itunes' as is_itunes; In this MySQL query, if "stuff" has the word "itunes" in it, it will mark it as itunes. However, I want to say "begin with". How can I check for "be…

  • How do I write this GROUP BY query in MYSQL?

    Suppose I have a column called "fruits" I want to select all of the top fruits, ranked by fruits (and group by + count). Fruits: orange orange apple banana apple apple In this case, the selec…

  • How do I execute this GROUP BY mysql query?

    Suppose I have a table called "Fruits" with a column called "name". name -------- apple orange orange orange apple grape How can I execute a query to produce this: orange 3 apple 2 grape…

  • How do I execute this query in MYSQL?

    Suppose I have a column with words: orange grape orange orange apple orange grape banana How do I execute a query to get the top 10 words, as well as their count?

  • How do I modify this query in MYSQL?

    SELECT title, title REGEXP 'apple' as is_fruit FROM mytable; TO: SELECT title, title REGEXP 'apple' or orange...or grapes...as is_fruit... Basically, how do I do an "OR" for REGEXP?

  • How do I get PHP variables from this MySQL query?

    I am working on an Asset Database problem using PHP / MySQL. In this script I would like to search my assets by an asset id and have it return all related fields. First I query the database as…

  • How do I convert this INNER JOIN query from SQL Server to MySQL?

    I'm currently migrating a customers application from ColdFusion on Windows with SQL Server to ColdFusion on Linux with MySQL and I'm running into some issues recreating their views with regards to j…