I'm doing a mysql query where I want to join two tables (Projects and Country):
+----------------+ +-------------------------+
| Project ID | | id |
+----------------+ +-------------------------+
| Name | | name |
+----------------+ +-------------------------+
| countryid | | lat, long (two columns) |
+----------------+ +-------------------------+
I'm using this query:
SELECT country.name, country.latitude, country.longitude
FROM projects JOIN country ON projects.countryid = country.id;
Fine until now! What happens next is each project has a class (TO-DO, DOING, DONE). Each class is one table that has all the project ids that belong to each.
The structure has to be like this, because each class has additional information. And that information only exists if the project belongs to that class. For example, a project in the TO-DO class has a "start_prevision_date" which is only valid in the TO-DO table (fields from other classes are still null).
What I want to do is assign one color to each project depending if he is TO-DO, DOING or DONE. A perfect solution would be a query that return values like:
ResultSet:
+--------------+------------------+-------------------+-------+-------+------+
| country.name | country.latitude | country.longitude | TO-DO | DOING | DONE |
+--------------+------------------+-------------------+-------+-------+------+
| Portugal | 13,4 | 15,6 | 1 | 0 | 0 |
+--------------+------------------+-------------------+-------+-------+------+
Can be in more than one column at once
Where 1 means he belongs and 0 means he doesn't belong. I could just check, and print the right color!
Is there a way to do this efficiently? Avoiding the use of extra three queries.
I hope I was clear.
You can use a query like this:
SELECT
country.name,
country.latitude,
country.longitude,
IF (TO-DO.sureField IS NULL, 0, 1) as TO-DO,
IF (DOING.sureField IS NULL, 0, 1) as DOING,
IF (DONE.sureField IS NULL, 0, 1) as DONE
FROM projects
LEFT JOIN country ON projects.countryid = country.id
LEFT JOIN TO-DO ON projects.projectid = TO-DO.projectid
LEFT JOIN DOING ON projects.projectid = DOING.projectid
LEFT JOIN DONE ON projects.projectid = DONE.projectid;
sureField has to be a field in each class table that is surely populated and not null when a project is in that class and null otherwise.
I tested in MySql reproducing a similar case and it worked.
This will give you:
ResultSet:
+--------------+------------------+-------------------+-------+-------+------+
| country.name | country.latitude | country.longitude | TO-DO | DOING | DONE |
+--------------+------------------+-------------------+-------+-------+------+
| Portugal | 13,4 | 15,6 | 1 | 1 | 0 |
+--------------+------------------+-------------------+-------+-------+------+
If the projects in DOING remain also in TO-DO, or:
ResultSet:
+--------------+------------------+-------------------+-------+-------+------+
| country.name | country.latitude | country.longitude | TO-DO | DOING | DONE |
+--------------+------------------+-------------------+-------+-------+------+
| Portugal | 13,4 | 15,6 | 0 | 1 | 0 |
+--------------+------------------+-------------------+-------+-------+------+
If the projects in DOING are deleted from TO-DO.
Therefore you will have to manage the query on result set appropriately.
Regards