If else statement mysql

Question

I have 4 tables :

• Town
• Department
• Region
• Country

A department have a foreign key to region and a region to Country

A town have 3 foreign key to the others tables but only one of them should be filled, the others have to be null. This is because you can reached the town's Country with its departement or region by following foreign keys.

Now i want to make a SQL query that return me all the towns with their department, region, country (if set). And the problem is that i cannot make differents joins based on which town's FK is set..

How to get that work ?

Answer 1

Here is an query to display the value.

select town.*,Department.*,Region.*,Country.* from town,Department,Region,Country where town.Dept_Id = Department.Dept and town.Region_Id = Region.Region_Id and town.Country_Id = Country.Countr_Id and Department.Region_Id = Region.Region_Id and Region.Country_Id = Country.Country_Id

Instead of this you can user Left Join Query. This query will take much more execute time. Left join query

select * from town left join Department ON Department.id = Town.department left join Region ON Region.id = coalesce (Town.region, Department.region) left join Country ON Country.id = coalesce (Town.country, Region.country)

Answer 2

How about this:

SELECT …
FROM Town
LEFT JOIN Department ON Department.id = Town.department
LEFT JOIN Region ON Region.id = COALESCE(Town.region, Department.region)
LEFT JOIN Country ON Country.id = COALESCE(Town.country, Region.country)

COALESCE takes the first non-NULL value from its arguments. So in this case, values from Town override other settings. As you state that a town which has a region reference won't have a department reference, only one of them should ever be non-NULL, so order does not matter if your data is as you say. The LEFT JOINs ensure that you get NULL values for tables which have no match. If all you need from the country is its id, you might instead omit the last join and include the COALESCE into the values you select.