How to use GROUP BY syntax in this multi-JOIN statement with a CASE statement?

Question

I have the following query

SELECT s.s_id, s.t_id, c.c_id, c.desc, sm.user_id
FROM s s
INNER JOIN c c
ON s.c_id=c.c_id
INNER JOIN sm sm
ON s.t_id = sm.t_id
WHERE s.c_id=8;

Which returns the following result set

s.s_id  s.t_id  c.c_id  c.desc     sm.user_id
3       123     8       something  2
3       123     8       something  2
3       123     8       something  1
4       456     8       something  2
4       456     8       something  2

I would like to

1. create an additional column in the result set that indicates whether a user owns the product (This involves using the CASE syntax)
2. and to only display those unique s.s_id (this involves using GROUP BY s.s_id)

For example if s.c_id=8 and sm.user_id=1 the result set would be

s.s_id  s.t_id  c.c_id  c.desc      sm.user_id does_user_own_product
3       123     8       something   1          yes
4       456     8       something   2          no

When s.s_id=3, the value of does_user_own_product is yes since there's at least one sm.user_id=1 WHERE s.s_id=3. When s.s_id=4, the value of does_user_own_product is no since there's no sm.user_id=1 WHERE s.s_id=4.

For example if s.c_id=8 and sm.user_id=2 the result set would be

s.s_id  s.t_id  c.c_id  c.desc      sm.user_id does_user_own_product
3       123     8       something   1          yes
4       456     8       something   2          yes

When s.s_id=3, the value of does_user_own_product is yes since there's at least one sm.user_id=2 WHERE s.s_id=3. When s.s_id=4, the value of does_user_own_product is yes since there's at least one sm.user_id=2 WHERE s.s_id=4.

What would be the appropriate query to achieve the above two subsets given I provide a value of s.c_id and sm.user_id

EDIT I realize there's some confusion about what it means for a users to own a product.

If a user's id can be found in sm.user_id then a user owns that s.s_id

So for example, in the original result set

s.s_id  s.t_id  c.c_id  c.desc     sm.user_id
3       123     8       something  2
3       123     8       something  2
3       123     8       something  1
4       456     8       something  2
4       456     8       something  2

Users 1 and 2 own s.s_id 3 and only user 2 owns s.s_id 4

Answer 1

Do this: http://www.sqlfiddle.com/#!2/e4c84/21

using MySql's strengths:

set @userInquired := 1;

select s_id, t_id, c_id, dsc, 
    bit_or(user_id = @userInquired) as does_user_own_product
from tbl
group by s_id;

set @userInquired := 2;

select s_id, t_id, c_id, dsc, 
    bit_or(user_id = @userInquired) as does_user_own_product
from tbl
group by s_id;

common denominator SQL:

set @userInquired := 1;

select s_id, t_id, c_id, dsc, 

  case when sum(case when user_id = @userInquired then 1 end) > 0 then
     1
  else
     0
  end as does_user_own_product

from tbl
group by s_id;


set @userInquired := 2;

select s_id, t_id, c_id, dsc, 

  case when sum(case when user_id = @userInquired then 1 end) > 0 then
     1
  else
     0
  end as does_user_own_product

from tbl
group by s_id;

---

Common denominator SQL. Shortest technique if your database don't have proper boolean, use combo of case when and max:

set @userInquired := 1;

select s_id, t_id, c_id, dsc, 

  max(case when user_id = @userInquired then 1 else 0 end) 
       as does_user_own_product

from tbl
group by s_id;



set @userInquired := 2;

select s_id, t_id, c_id, dsc, 

  max(case when user_id = @userInquired then 1 else 0 end) 
       as does_user_own_product

from tbl
group by s_id;

Answer 2

Maybe like this:

SELECT s.s_id, s.t_id, c.c_id, c.desc, sm.user_id,
  MAX(sm.user_id = @userid) AS does_user_own_product
FROM s s
INNER JOIN c c
ON s.c_id=c.c_id
INNER JOIN sm sm
ON s.t_id = sm.t_id
WHERE s.c_id=8
GROUP BY s.s_id;

Although, to be honest, I do not see much point in pulling columns (like c.c_id, c.desc, sm.user_id) that are neither included in GROUP BY nor aggregated. (Yes, MySQL does allow you to do this, but those values don't seem to make much sense in your case.)