CODEWITHSUNDEEP
mysql
user2433018
user2433018

Reputation: 23

SQL select values from joined table where value and value appears

I have this MySQL tables:

and I'd like to get all the tracks that contains station A and station B... I just can't figure it out :S I tried like this:

SELECT track.name
FROM track, station_track , station 
 WHERE station.id = station_track.id_station 
  AND track.id = station_track.id_track 
  AND (station.name = 'first' AND station.name = 'second')

It doesn't work... I guess I can't use AND for same column values.

SOLVED:

SELECT proge.ime FROM postaje, postaje_proge, proge
WHERE postaje.id=postaje_proge.ID_postaje AND proge.ID=postaje_proge.ID_proge 
AND postaje.ime_postaje in ("AP Mlinska","City Center")
GROUP BY proge.ime
HAVING count(proge.ime)=2

tnxx guys for help!

Upvotes: 1

Views: 1696

Answers (3)

user359040
user359040

Reputation:

Assuming you want tracks linked to both stations A and B, try:

SELECT t.name 
FROM track t
JOIN station_track st on t.id=st.id_track 
JOIN station s on s.id=st.id_station and s.name in ('first','second')
GROUP BY t.name
HAVING count(distinct s.name)=2

Upvotes: 0

j883376
j883376

Reputation: 1135

Your final condition, (station.name="first" AND station.name="second"), is checking that one variable is equal to two different values at the same time which will never evaluate to true.

If you want to find all stations with the names first or second, you need to use an OR statement.

SELECT track.name FROM track, station_track , station 
WHERE station.id=station_track.id_station 
AND track.id=station_track.id_track 
AND (station.name="first" OR station.name="second")

Upvotes: 3

dnshio
dnshio

Reputation: 924

You will have to do a bit of thinking and redesign your tables. If you want to match a field to a list of values you can use the 'in' operative

i.e 

SELECT * FROM station_track WHERE id_track in (1, 2, 3, 4);

The above will return all records matching any of the values in the list. So, think about how you could take advantage of this and redesign your schema.

Upvotes: 0

Related Questions