CODEWITHSUNDEEP
mysqlsqlsubquery
jose
jose

Reputation: 1562

Subquery. Obtain additional criteria on each user

In the following example to illustrate my problem, on rows to be displayed on a grid, Marge can see row 1, Bart row 2, and Homer rows 1 and 2.

If the user is Marge and Marge is on users_list she can see row 1:

 //tab_rows

 id  rows   user_ref
 __  ____   ________

  1   row1    1
  2   row2    2

.

 //tab_users

  id_users   user_ref   users_list    color
  ________   _______   __________   _______

    1          1         Marge        Red
    2          1         Homer        Blue
    3          2         Bart         Black
    4          2         Homer        Green

.

 SELECT rows FROM tab_rows 
 WHERE user_ref IN (SELECT user_ref FROM tab_users 
                    WHERE users_list = 'Marge')

This works.

My problem is that I also need to get the values from the color column on the client side with the following purpose:

In the example, Marge and Homer can see both row 1.

However if the user is Marge the row will be higlighted in red; if the user is Homer row will be higlighted in blue.

(Each user can have different colors on different rows as happens with Homer).

On the client side I will use a render function to do this dynamically according to the value of the color.

What is the best way to do this?

I need a second query to get the color that corresponds to each of the users in each row?

Upvotes: 1

Views: 24

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a join 

select a.rows, b.color
from tab_rows as a 
inner join  tab_users as  b on a.user_ref = b.user_ref 
where b.userlist = 'Marge';

Upvotes: 1

Related Questions