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

Question

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

Answer 1

You can do it by getting the maximum value of day_has_activities for each id_travel and then join this dataset 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

Answer 2

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
 ;

Answer 3

Knowing that 'yes' > 'no' for strings, you can just select MAX(td.day_has_activities) while grouping by the other two columns (making them "distinct").

SELECT t.id_travel, u.name, max(td.day_has_activities) as 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
group by t.id_travel, u.name

id_travel	name	has_activities
1	Paul	yes
2	Paul	no
3	Caroline	yes

fiddle

If it's possible to have no matches at all in the travel_days (That's why you use a LEFT join - right?), you can use coalesce(max(td.day_has_activities), 'no'), making 'no' the default value.

Another way is to check for an existence of a related row with 'yes' using a subquery in the SELECT clause (subselect):

SELECT 
  t.id_travel, 
  u.name,
  case
    when exists (
      select *
      from travel_days td
      where td.id_travel = t.id_travel
        and td.day_has_activities = 'yes'
    ) then 'yes' else 'no' 
  end as has_activities
FROM travel t
left join users u on u.id_user = t.id_user

This can avoid scanning the entire table, if there are only few matches.