This question is kind of a extention to my previous post :
Multi-Table Key search
I have a SELECT with numerous LEFT JOINs all checking to see if a value is in the table. This is fine so long as there is a maximum of one match per table but if there is more than one match the other tables repeate their one match.
eg. Here I have inserted dummy data into my DB so as to produce a good example.
Code:
mysql> SELECT church.churchName, church.churchID, event.eventName, event.eventID, college.collegeName,
college.collegeID, regionaloffice.officeName, regionaloffice.officeID, centre.centreName, centre.centreID
FROM person LEFT JOIN church ON personID = church.contactID
LEFT JOIN event ON person.personID = event.contactID
LEFT JOIN college ON person.personID = college.contactID
LEFT JOIN regionaloffice ON person.personID = regionaloffice.contactID
LEFT JOIN centre ON person.personID = centre.contactID
WHERE personID = 106 group by churchID;
+-----------------------+----------+-----------+---------+-------------+-----------+------------+----------+------------+----------+
| churchName | churchID | eventName | eventID | collegeName | collegeID | officeName | officeID | centreName | centreID |
+-----------------------+----------+-----------+---------+-------------+-----------+------------+----------+------------+----------+
| Orpington Town Church | 133 | test | 1 | temp | 1 | test | 1 | NULL | NULL |
| Acorn Church | 397 | test | 1 | temp | 1 | test | 1 | NULL | NULL |
+-----------------------+----------+-----------+---------+-------------+-----------+------------+----------+------------+----------+
2 rows in set (0.01 sec)
As you can see here, the second row matches the same as the first, with the exception of the 'Church' as it has two destinct records.
This is not a common occurance and what I have would work fine in the majority of cases. But I am leaning SQL and would like to have a solution that is an Every Case solution, not a Most Case solution.
I have no problems in re-writing the SQL with an entirely different approch.
Any help would be great.
Indego
dBforums
>
Removing Duplicate Hits from Join
02-09-04, 15:08
Removing Duplicate Hits from Join
Linear Mode
