Question 325338 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL: Query multiple tables with a conditional result field (Sorry I don't even know how to ask it correctly)

Taking into account the following example tables:

USERS:

id_user name
1 Paul
2 Caroline

TRAVEL:

id_travel id_user
1 1
2 1
3 2

TRAVEL_DAYS:

id_tdays id_travel day_has_activities
1 1 no
2 1 no
3 1 yes
4 2 no
5 2 no
6 3 no
7 3 yes
8 4 yes

If I do the following query:

SELECT 
t.id_travel,
u.name,
td.day_has_activities
FROM travel t
left join users u on u.id_user = t.id_user
left join travel_days td on td.id_travel = t.id_travel

Since trip number one has three days and trip two, as well as trip three, have two days each the result would be something like this:

id_travel id_user day_has_activities
1 Paul no
1 Paul no
1 Paul yes
2 Paul no
2 Paul no
3 Caroline no
3 Caroline yes

I need to have unique trip rows, so I thought about using DISTINC but the has_activities field shows me "yes" or "no" randomly.

I need to know for each trip if it contains any days with activities so the perfect result would be this :

id_travel id_user travel_has_activities
1 Paul yes
2 Paul no
3 Caroline yes

I tried something like this, but it doesn’t runs:

SELECT 
t.id_travel,
u.name,
if ((select count(td.day_has_activities) from t where td.day_has_activities like 'yes') > 0, 'yes', 'no') as travel_has_activities
FROM travel t
left join users u on u.id_user = t.id_user
left join travel_days td on td.id_travel = t.id_travel
Tags:

2

Answers


  1. 0

    You should obtain the maximum value of day_has_activities for each id_travel and then join it with your tables using a LEFT JOIN.

    select t.id_travel, u.name, case when day_has_activities = 1 then 'yes' else 'no' end as travel_has_activities
from travel t
left join users u on u.id_user = t.id_user
left join (
  select id_travel, max(case when day_has_activities = 'yes' then 1 else 0 end ) as day_has_activities
  from travel_days
  group by id_travel
) td on td.id_travel = t.id_travel

    Demo here

    Login or Signup to reply.
  2. 0

    CTE part, I used your query to joins table, and second part I used self join and exist function to check yes values exist in previous table or not and group by according to first and second table

    WITH example as (
SELECT 
t.id_travel,
u.name,
td.day_has_activities
FROM travel t
left join users u on u.id_user = t.id_user
left join travel_days td on td.id_travel = t.id_travel
)


SELECT
    id_travel,
    id_user,
    CASE WHEN EXISTS (
        SELECT 1
        FROM example AS t2
        WHERE t2.id_travel = t1.id_travel AND t2.day_has_activities = 'yes'
    ) THEN 'yes' ELSE 'no' END AS day_has_activities
FROM
    example AS t1
GROUP BY
    id_travel,
    id_user
ORDER by id_travel,
id_user
 ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search