CODEWITHSUNDEEP
mysqlsql
user1080247
user1080247

Reputation: 1166

how to do multi conditional join

i have sql query which search in two tables.city and country

the city come with two language and also the country

so i have 4 city coulmns City and ar_city ,country and a_country

because i have not expected what user use any lang so i use condition or

but i have sql error..so how to do this

select * FROM d_cities
WHERE City='algeria center'
OR ar_city='الجزائر العاصمة الجزائر' 
JOIN d_country 
ON d_country.Country = 'algeria' 
OR d_country.ar_country='ألجزائر'

Upvotes: 0

Views: 56

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

For inner joins, the predicates (conditions) can be in the join's on clause, (below assumes the ar_city and city columns are in d_cities). 

Select * FROM d_cities c
   JOIN d_country n 
     ON (n.Country = 'algeria' 
        Or n.ar_country='ألجزائر')
     And (c.City = 'algeria center'
       Or c.ar_city = 'الجزائر العاصمة الجزائر')

or in the query's Where clause But the where clause must come after the joins.

Select * FROM d_cities c
   JOIN d_country n 
     ON n.Country = 'algeria' 
        Or n.ar_country = 'ألجزائر'
WHERE City = 'algeria center'
   OR ar_city = 'الجزائر العاصمة الجزائر'

Upvotes: 1

David M
David M

Reputation: 72870

You need to place the WHERE clause after the JOIN:

select d_cities.* FROM d_cities
JOIN d_country 
ON d_country.Country = 'algeria' 
OR d_country.ar_country='ألجزائر'
WHERE d_cities.City='algeria center'
OR d_cities.ar_city='الجزائر العاصمة الجزائر'

You can return any column from either table - this version returns all from d_cities only.

Upvotes: 0

Related Questions