Return rows which contain times greater than one hour

Question

I have this query:

SELECT s.* FROM  stop s 
WHERE 
s.hour>
        (SELECT st.hours FROM stop st
             JOIN City cit ON cit.id = st.idCity
             WHERE cit.name = 'LA' )

But the subquery returns more than one row, how should I proceed?

City

id	name
1	LA
2	Ny
3	AZ

Stop

id	Hour	idCity	idCityB
1	7:00	1	2
2	8.00	2	1
3	9:00	3	2
5	10:00	1	2
6	11:00	3	2

I want 8:00, 9:00 and 11:00

share schema for table and sample data. Also, share the expected output from sample data. — Amit Verma, Jan 20 '22 at 08:40
You should use correlated subquery. I.e. you must add a condition to the subquery which selects the data for definite group defined by some value of the currectly processed outer row (from `stop AS s` table copy). And the value returned by the subquery must be aggregated one (for example, `MAX(st.hours)`). — Akina, Jan 20 '22 at 08:42
@gianluca23 what is `fer.localita` you haven't included fer table ? — Ergest Basha, Jan 20 '22 at 09:23
*with max, it doesn't return all the value* Have you added correlation condition into the subquery? — Akina, Jan 20 '22 at 09:25
@Akina I think so, what do you mean when you say: " synchronize all columns names in the query and in data sample"? — gianluca23, Jan 20 '22 at 09:35
@gianluca23 I can't understand the logic, maybe is this https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=13e6de0f4b36c804546a2a6e167a7856 what you are trying ? — Ergest Basha, Jan 20 '22 at 09:36
@ErgestBasha It isn't 'not in' because the query is for the bus stops if I have a bus stop before the main bus stop, the query must not take it. Sorry for the omission it was not done on purpose — gianluca23, Jan 20 '22 at 09:48
@Akina now is all sync. Sorry, it's my first question and I'm not english — gianluca23, Jan 20 '22 at 10:01

score 0 · Answer 1 · answered Jan 20 '22 at 10:09

Possible realizations (use those which matches needed logic if present):

SELECT s.* 
FROM stop s
JOIN stop st USING (`hour`)
JOIN city cit ON cit.id = st.idcity
WHERE cit.name <> 'LA'

SELECT s.* 
FROM  stop s 
WHERE hour NOT IN ( SELECT st.`hour` 
                    FROM stop st 
                    JOIN city cit ON cit.id = st.idcity
                    WHERE cit.name = 'LA' )

On your sample data both queries gives the same desired output, but their logic differs and they will give different output on another data.

Return rows which contain times greater than one hour

1 Answers1