Re: Need help to create a complex query
Yes, it is possible, but probably you would be a lot better off if you would touch up your table structure.
As for a solution to your question: if you can be sure that the project_location_id always has this format than you could use the function LOCATE(substr,str,pos)
to find out where the dots are at in your project location id. In combination with SUBSTRING(str,pos,len), you could cut your project_location_id into the bits between the dots, convert that to numbers with CAST or CONVERT and then use those to find the corresponding records in the tables.
The reverse is also possible (but yet less wise still): generate all combinations of id's of your tables using crossjoins, concatenate those id's, separated by dots and compare that to your project_location_id.
But enough of this sillyness. Look it up in the manual if you want to try it, but I really recommend that you alter your tables. I dont know much about districts, provinces, states, countries and projects, but the intention seems to be that:
1) a state always belongs to one country, but a country can have many states
2) a province always belongs to one state, but a state can have many provinces
3) a district always belongs to one province, but a province can have many districts
4) a project location corresponds to only one district, but many projects can be located in one district.
If that is the case then:
1) add a country_id to your state table, and store the country_id along with your state
2) add a state_id to your province table, and store the state_id along with your province
3) add a province_id to your district table and store the province_id along with your district
4) add a district id to your project table and store the district id along with the project.
Once you did all that, you can write a very efficient inner join like this
select p.id
, d.name
, p.name
, s.name
, c.name
from project p
inner join district d on p.district_id = d.district_id
inner join province p on d.province_id = p.province_id
inner join state s on p.state_id = s.state_id
inner join country c on state.country_id = c.country_id
where p.project_id = 1
Good luck!