mysql – Combine many joins into one join? – Education Career Blog

Question 1

There are two tables – incoming tours(id,name) and incoming_tours_cities(id_parrent, id_city) where id_parrent is id from first table.

Here is the query i wrote

SELECT t.cities
FROM `incoming_tours` t
JOIN `incoming_tours_cities` tc0 ON tc0.id_parrent = t.id
AND tc0.id_city = '1'
JOIN `incoming_tours_cities` tc1 ON tc1.id_parrent = t.id
AND tc1.id_city = '6'

And now, what is the question…

Why i can’t write both conditions in single join?(i.e. i can, but it returns empty result.)

as i understand joins, when i wrote

JOIN incoming_tours_cities tc ON tc.id_parrent = t.id

it must return the list of rows where the condition is true. isn’t it?

So why i can’t write

SELECT t.cities
FROM `incoming_tours` t
JOIN `incoming_tours_cities` tc ON tc.id_parrent = t.id
AND tc.id_city = '1'
AND tc.id_city = '6'

And maybe there is more efficient method to rich same effect(because in my structure the count of conditions can be very big)

Thanks much

Question 2

the value of tc.id_city cannot be both ‘1’ and ‘6’ simultaneously. I think you want an OR rather than an AND:

SELECT t.cities 
FROM `incoming_tours` t 
JOIN `incoming_tours_cities` tc ON tc.id_parrent = t.id 
AND (tc.id_city = '1' 
OR tc.id_city = '6')

Question 3

Think of it this way. If you ask for rows from incoming_tour_cities for which the id_city value is ‘1’ and is also at the same time ‘6’, how many rows will you match?

What you really want is:

 SELECT t.cities
 FROM `incoming_tours` t
 JOIN `incoming_tours_cities` tc ON tc.id_parrent = t.id
 WHERE (tc.id_city = '1'  OR  tc.id_city = '6')

or, more compactly:

 SELECT t.cities
 FROM `incoming_tours` t
 JOIN `incoming_tours_cities` tc ON tc.id_parrent = t.id
 WHERE tc.id_city IN ('1', '6')

Question 4

An alternative answer based on the user’s clarification that the first query is the one he wants to duplicate.

Here is the only “short cut” way I know of doing this, where “short cut” means not performing two independent tests (using JOINs or EXISTs clauses):

 SELECT t.cities
  FROM `incoming_tours` t
  JOIN `incoming_tours_cities` tc ON tc.id_parrent = t.id
  WHERE tc.id_city IN ('1', '6')
  GROUP BY t.cities HAVING COUNT(DISTINCT tc.id_city) > 2

Leave a Comment Cancel reply