1 Replies - 987 Views - Last Post: 28 August 2012 - 04:32 PM

#1 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Help with a 3 table LEFT JOIN

Posted 28 August 2012 - 03:45 PM

Hello Everyone and Good Day...

So I have this situation very similar to my recent post here, http://www.dreaminco...loops-problems/

The difference is that this time I have 3 tables involved instead of 2... Here's what I have...

Here is the first query which I have put into a LEFT JOIN, that encompasses 2 of the tables...

$query = "SELECT DirectExaminerCompaniesExaminers.ExaminerID, Examiners.ExaminerName 
            FROM DirectExaminerCompaniesExaminers 
            LEFT JOIN Examiners 
            ON DirectExaminerCompaniesExaminers.ExaminerID = Examiners.ExaminerID 
            WHERE DirectExaminerCompaniesExaminers.DirectExaminerCompanyID = '{$_SESSION['DirectExaminerCompanyID']}'";



Here is the second query that I want to add to that query and make it like the query on my previous post...

$query = "SELECT ExaminerID FROM ExaminerZipCodes WHERE ExaminerZipCode = '{$row['OrderTicketZipCode']}'";



The ultimate goal is to be able to put in the SELECTED tag in the output list of options if one of the results from the first query matches one of the results from the second query...

Here's how it was showed to me on my previous post... (if it was only 2 tables I think I could have figured it out)

$query = "SELECT rt.RequirementType, (!ISNULL(otr.OrderTicketID)) AS 'checked'
            FROM RequirementTypes AS rt
            LEFT JOIN OrderTicketRequirements AS otr
            ON rt.RequirementType = otr.OrderTicketRequirement
            AND otr.OrderTicketID = '{$row['OrderTicketID']}' 
            ORDER BY rt.RequirementTypeOrder ASC";



Is This A Good Question/Topic? 0
  • +

Replies To: Help with a 3 table LEFT JOIN

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: Help with a 3 table LEFT JOIN

Posted 28 August 2012 - 04:32 PM

If I understand you correctly, what you want is for the first result set to be filtered, so that a row passes through the filter if and only if the ExaminerID is in the second result set. If not, please post back clarifying what you mean.

To filter results out, you use an INNER JOIN. The difference between a LEFT OUTER JOIN and an INNER JOIN is that a LEFT OUTER JOIN will retain the row if there is no match, but an INNER JOIN will remove the row if there is no match. I think this is the code you are after:
$query = "SELECT DirectExaminerCompaniesExaminers.ExaminerID, Examiners.ExaminerName 
            FROM DirectExaminerCompaniesExaminers 
            LEFT OUTER JOIN Examiners 
				ON DirectExaminerCompaniesExaminers.ExaminerID = Examiners.ExaminerID 
			INNER JOIN ExaminerZipCodes
				ON ExaminerZipCodes.ExaminerID = Examiners.ExaminerID
            WHERE DirectExaminerCompaniesExaminers.DirectExaminerCompanyID = '{$_SESSION['DirectExaminerCompanyID']}' 
			AND ExaminerZipCodes.ExaminerZipCode = '{$row['OrderTicketZipCode']}'";


The ON clause for that INNER JOIN could be against Examiners.ExaminerID or DirectExaminerCompaniesExaminers.ExaminerID, and will produce different results in each case. For instance, if a row in DirectExaminerCompaniesExaminers has an ExaminerID that is not in Examiners, but is in ExaminerZipCodes, it will show up in the former case but not the latter.

This is the nature of LEFT OUTER vs INNER JOINs. It would be wise to read up about different JOIN clauses. As much as a dislike spruiking them, W3Schools have good tutorials on INNER JOINs, LEFT OUTER JOINs, RIGHT OUTER JOINs (which aren't necessary to use) and FULL JOINs. Unfortunately, they don't have anything on CROSS JOINs, but that is a rarely used JOIN clause. Also unfortunately, I can't point you towards any tutorials here or elsewhere, apart from the W3Schools ones. I may have to write a tutorial on JOINs so that we have a resource.

In terms of PHP, I would also strongly advise moving away from mysql_* functions, as they are no longer supported, arewide open to injection, and so could compromise your entire database. There are two tutorials here by Dormilich that are well worth the read - Introduction to PDO and Be Prepared for your Database.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1