CODEWITHSUNDEEP
mysqlsqljoin
Andris
Andris

Reputation: 1442

MySQL multiple inner join between 2 tables on different columns for one of the tables

Table transport

Id  | FirstLevSubcat | SecondLevSubcat | ThirdLevSubcat 
--------------------------------------------------------
 8  |   4            |   27            |  1418

Table categories

Id    | CategoriesUrl
--------------------
 4    |   cars          
 27   |   audi          
 1418 |   audi-100

Query if not to use categories table (without inner join) would be like

SELECT count(*) FROM transport
WHERE FirstLevSubcat = 4 AND SecondLevSubcat = 27 AND ThirdLevSubcat = 1418

Trying to get the same result using INNER JOIN

SELECT count(*) FROM transport main_table 
INNER JOIN categories cat_table_first ON cat_table_first.IdRows = main_table.FirstLevSubcat 
INNER JOIN categories cat_table_second ON cat_table_second.IdRows = main_table.SecondLevSubcat 
INNER JOIN categories cat_table_third ON cat_table_third.IdRows = main_table.ThirdLevSubcat 
WHERE 
cat_table_first.CategoriesUrl = 'cars' 
AND cat_table_second.CategoriesUrl = 'audi' 
AND cat_table_third.CategoriesUrl = 'audi-100'

At first sight all works

But is such query ok? May be can improve something?

Upvotes: 1

Views: 36

Answers (1)

rtruszk
rtruszk

Reputation: 3922

Your query is correct. You can also do it in following way:

SELECT count(*) FROM transport main_table 
INNER JOIN categories cat_table_first ON cat_table_first.IdRows = main_table.FirstLevSubcat and cat_table_first.CategoriesUrl = 'cars'
INNER JOIN categories cat_table_second ON cat_table_second.IdRows = main_table.SecondLevSubcat and cat_table_second.CategoriesUrl = 'audi' 
INNER JOIN categories cat_table_third ON cat_table_third.IdRows = main_table.ThirdLevSubcat and cat_table_third.CategoriesUrl = 'audi-100'

You can also do it using 3 EXISTS block. 

SELECT count(*) FROM transport main_table 
WHERE 
EXISTS (SELECT NULL FROM categories WHERE main_table.FirstLevSubcat=categories.IdRows AND categories.CategoriesUrl ='cars') 
AND
EXISTS (SELECT NULL FROM categories WHERE main_table.SecondLevSubcat=categories.IdRows AND categories.CategoriesUrl ='audi') 
AND
EXISTS (SELECT NULL FROM categories WHERE main_table.ThirdLevSubcat=categories.IdRows AND categories.CategoriesUrl ='audi-100')

Upvotes: 1

Related Questions