select combination of two columns sql, h2

Question

Hello i have this table:

In this table i want to check, if the combination of the columns: 'carrier' and 'flight' always give the same origin. How can i do that?

I tried this one:

select distinct(a.name)
from carriers c, flights f, airports a
where c.id = f.carrier and a.id = f.origin;

but i dont think it's giving me what i wanted to know. Really grateful for any advice.

Unrelated to your problem, but: `distinct` is **not** a function. It always applies to all columns in the select list. Enclosing one of the columns with parentheses won't change anything and is useless. `distinct (a),b` is the same as `distinct a,(b)` or `distinct a,b` — , Jul 05 '20 at 19:49
Why are you choosing not to use proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Jul 05 '20 at 21:30

score 0 · Accepted Answer · answered Jul 05 '20 at 18:19

If this query returns any rows, then you'll get exactly which combination of carrier + flight has multiple origins

select carrier, flight, count(distinct origin) as number_of_origins
from flights
group by carrier, flight
having count(distinct origin) >= 2;

score 0 · Answer 2 · answered Jul 05 '20 at 18:19

0

This query tells you if there are any carrier + flight combinations related to more than one origin:

select carrier, flight
from flights
group by carrier, flight
having count(distinct origin) > 1

answered Jul 05 '20 at 18:19

The Impaler

2 Answers2