Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Question Unanswered: Removing Duplicate Hits from Join

    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

  2. #2
    Join Date
    Jan 2004
    Posts
    8
    When all else fails.

    I have changed this to simply use multiple select statements. One for each table I was 'Join'ing.

    As an aside, with PHP do most coders do a check if a select has returned without errors for EVERY mysql_query() call?

    eg
    Code:
    .
    .
    .
    $test = mysql_query("Select * from test");
    if(!$test)
    {
      echo 'Database Connection Problem';
    }
    else
    {
    ...
    }
    .
    .
    Or are most coders lazy and leave out the error checking?

    Indego

  3. #3
    Join Date
    Nov 2003
    Posts
    18

    Re: Removing Duplicate Hits from Join

    Old post but what the heck.

    You can use DISTINCT in the SELECT statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •