What is the query for getting user location from this database schema?

I have 4 tables – USERS, CITIES, STATES, COUNTRIES

USERS( user_id, email, password, name, location(FK) [references city_id] )

CITIES( city_id, name, state_id(FK), country_id(FK) ) 

STATES( state_id, name, country_id(FK) )

COUNTRIES( country_id, name )

​x
 
USERS( user_id, email, password, name, location(FK) [references city_id] )​CITIES( city_id, name, state_id(FK), country_id(FK) ) ​STATES( state_id, name, country_id(FK) )​COUNTRIES( country_id, name )​

I want to select username with complete location (eg: ADAM New Delhi, Delhi, India)

What is the optimal query for this?

Answer

Try this below script

SELECT A.name USERS,
B.name CITIES,
C.name STATES,
D.name COUNTRIES
FROM USERS A
INNER JOIN CITIES B ON A.location = B.city_id
INNER JOIN STATES C ON B.state_id = C.state_id
INNER JOIN COUNTRIES D ON C.country_id = D.country_id

 
SELECT A.name USERS,B.name CITIES,C.name STATES,D.name COUNTRIESFROM USERS AINNER JOIN CITIES B ON A.location = B.city_idINNER JOIN STATES C ON B.state_id = C.state_idINNER JOIN COUNTRIES D ON C.country_id = D.country_id​

Advertisement

Answer