Multiple Inner Join Problem

I can do one inner join, but get an error when attempting two

[familyman]

I need to display info from multiple tables. I found this post:

[webmasterworld.com...]

and I was able to successfully complete an inner join. Unfortunately I get an error when I try to perform two inner joins in one statement.

I would really appreciate it if someone would take a look and tell me what I'm doing wrong.

THIS WORKS

$query = "SELECT
tblDocuments.documentID,
tblDocumentTypes.documentTypeID,
tblDocumentTypes.documentTypeName
FROM tblDocuments
INNER JOIN tblDocumentTypes USING (documentTypeID)";

THIS WORKS

$query = "SELECT
tblDocuments.documentID,
tblAgents.agentID,
tblAgents.firstName
FROM tblDocuments
INNER JOIN tblAgents USING (agentID)";

THIS DOESN'T

$query = "SELECT
tblDocuments.documentID,
tblAgents.agentID,
tblAgents.firstName,
tblDocumentTypes.documentTypeID,
tblDocumentTypes.documentTypeName
FROM tblDocuments
INNER JOIN tblAgents USING (agentID)
INNER JOIN tblDocumentTypes USING (documentTypeID)";

Again I really appreciate it. I've spent half a day searching and I greatly appreciated the forementioned post!

Thanks,
Phil

[coopster]

Welcome to WebmasterWorld, familyman!

First off, I want to state that it sure is nice to know previous posts are being searched, found and applied. Good for you! I admire your resolve!

Let me guess, you are getting the "BAD FIELD ERROR" or "Unknown column in ON clause", right? I'll show you the problem area, explain the issue, and then how to fix it.

---

problem:

$query = "SELECT
tblDocuments.documentID,
tblAgents.agentID,
tblAgents.firstName,
tblDocumentTypes.documentTypeID,
tblDocumentTypes.documentTypeName
FROM tblDocuments
INNER JOIN tblAgents USING (agentID)
INNER JOIN tblDocumentTypes USING (documentTypeID)";

---

cause:

When using more than two tables the first table listed is referred to as the left table and the others right tables. Normally

JOIN

statements are processed from left to right unless controlled by other clauses such as

USING

,

ON

, parentheses, etc.

You are getting an error because your query is trying to

JOIN

tblDocumentTypes

to

[b]tblAgents[/b]

using

documentTypeID

Whereas what you really want is to

JOIN

tblDocumentTypes

to

[b]tblDocuments[/b]

using

documentTypeID

How come the multiple INNER JOIN worked in the other example you referred to?
If you have a closer look you will notice that each table was joining the previous table listed in succession, left to right.

---

resolution:

$query = "SELECT
tblDocuments.documentID,
tblAgents.agentID,
tblAgents.firstName,
tblDocumentTypes.documentTypeID,
tblDocumentTypes.documentTypeName
FROM tblDocuments
INNER JOIN tblAgents USING (agentID)
INNER JOIN tblDocumentTypes ON
(tblDocuments.documentTypeID=tblDocumentTypes.documentTypeID)";

If it is a specific table you are joining, you need to use the ON clause so that you can fully qualify the column name with a table name in your

JOIN

clause. Like column names in tables must use fully qualified column names. Qualified is the

dot

notation that combines the names of databases, tables and columns. In this example,

tblDocuments

and

tblDocumentTypes

both contain the column

documentTypeID

. To qualify each, we would refer to them as

tblDocuments.documentTypeID

and

tblDocumentTypes.documentTypeID

.

[familyman]

Awesome!

Thank you so much. It only took me 5 minutes to do it with your help.

Thanks again,
and again.

[familyman]

I appreciated that last bit of help so much, I almost feel bad asking again... but here goes.

I was having a really hard time applying your advice to the following situation:

Table: tblProperties
Fields: propertyID
address
city
state
zip
buyingAgentID
sellingAgentID

Table: tblAgents
Fields: agentID
email
password
firstName
lastName

I am trying to display the contents of tblProperties, pulling the first and last name of 1 agent from tblAgents for buyingAgent and pulling the first and last name of 1 agent from tblAgents for sellingAgent.

The part that was getting me is that I need the names of 2 agents per record in tblProperties.

I got MySQL, Second Edition By Paul DuBois, and was thoroughly dizzied. Too much information.

[coopster]

Although you have two agents per record in your

tblProperties

table, you only have one per record in your

tblAgents

table. The simplest solution would be to execute two separate queries to get the names of each. However, if you really want the result set returned in one row, you are going to have to JOIN the

tblAgents

table to itself. And it will get a bit more complex if the Buying Agent and the Selling Agent could be the same person! That's why we are going to introduce the IF control flow:

SELECT 
 ta1.firstName as buying_agent_firstName, 
 ta1.lastName as buying_agent_lastName, 
 ta2.firstName as selling_agent_firstName, 
 ta2.lastName as selling_agent_lastName, 
 FROM tblAgents as ta1, tblAgents as ta2 
 INNER JOIN tblProperties ON (buyingAgentID = ta1.id OR sellingAgentID = ta2.id) 
 WHERE IF(buyingAgentID <> sellingAgentID, ta1.id <> ta2.id, ta1.id = ta2.id) 
; 

What a mouthful, huh?

[familyman]

When you say "The simplest solution would be to execute two separate queries to get the names of each" do you mean:

-Copy tblProperties into $arrayProperties

-"SELECT agentID,firstName,lastName FROM tblAgents"
----cycle through $arrayProperties, resolving
----each agentID to firstName and lastName

I tried something like that before to solve my original problem (the one that started this post) and the performance was terrible.

I've seen queries that use temporary tables. Would that work?

[coopster]

I'd like to retract that statement :)

I'm not sure that is a very good suggestion. I forgot you were looping through the

tblProperties

table. I was thinking along the lines of...

while (looping through tblProperties) { 
 $buyingAgentID = $row['buyingAgentID']; 
 $sellingAgentID = $row['sellingAgentID']; 
 $sql = "SELECT lastName, firstName FROM tblAgents WHERE buyingAgentID = '$buyingAgentID'; 
 $agentRows = mysql_query($sql); 
 $agentRow = mysql_fetch_assoc($agentRows); 
 $buyingAgentLastName = $agentRow['lastName']; 
 $buyingAgentFirstName = $agentRow['firstName']; 
 mysql_free_result($agentRows); 
 $sql = "SELECT lastName, firstName FROM tblAgents WHERE sellingAgentID = '$sellingAgentID'; 
 $agentRows = mysql_query($sql); 
 $agentRow = mysql_fetch_assoc($agentRows); 
 $sellingAgentLastName = $agentRow['lastName']; 
 $sellingAgentFirstName = $agentRow['firstName']; 
 mysql_free_result($agentRows); 
} 

...but that is just not looking good to me. Did you try the JOIN TABLE to itself option posted earlier? I tested it and it works fine. I'm not sure how big your tables are, but I'm willing to bet MySQL will handle this faster than forcing the PHP parser to do more once the result set is returned. I tend to try and keep as much of the data processing in the database server.

[familyman]

$aAgents = array();

$aAgents[0] = 'None';

$sql = 'SELECT
agentID,
firstName,
lastName
FROM tblAgents';

include('./misc/db.php');

while ($db = mysql_fetch_array($db_result)) {

$vAgentID = $db['agentID'];

$aAgents[$vAgentID] = $db['firstName'].' '.$db['lastName'];

}

$sql = 'SELECT
tblProperties.propertyID,
tblProperties.address,
tblProperties.city,
tblProperties.state,
tblProperties.zip,tblProperties.buyingAgentID,tblProperties.sellingAgentID
FROM tblProperties';

include('./misc/db.php');

while ($db = mysql_fetch_array($db_result)) {
$vBuyingAgentID = $db['buyingAgentID'];
$vSellingAgentID = $db['sellingAgentID'];
echo('<tr><td>'.$db['propertyID'].'</td><td>' .$db['address'].'</td><td>'.$db['city'].'</td><td>' .$db['state'].'</td><td>'.$db['zip'].'</td><td>' .$aAgents[$vBuyingAgentID].'</td><td>'.$aAgents[$vSellingAgentID] .'</td><td></td></tr>');
}

This seems to work well and does not seem to cause performance issues. Do you see a problem with me doing it this way?

[edited by: jatar_k at 5:16 am (utc) on Feb. 5, 2004]
[edit reason] fixed sidescroll [/edit]

[coopster]

Whatever works best for you, my friend :)

The code you show here is doing the same thing the bad-suggestion code I showed prior in message #7. Well, let me rephrase that. The array processing you have is going to be better than the multiple database calls within the loop, that's for sure. However, both have the additional overhead of functional processing within the main processing loop -- you still have to get the names of the agents from an additional result set, whether that be in the form of an additional query or in a preloaded array which was the result of an additional query.

As I stated before, I tend to push as much of the data selection and arrrangement back on the database server as I possibly can. By joining the tblAgents to itself, we can accomplish the necessary task of retrieving the Buying and Selling Agent names for one parcel of property and return them both on the same row of the result set. The JOIN eliminates any need to run a second query and load up an array in which you will perform a lookup twice for every iteration of your outer loop again.

To summarize, yes, your code looks like it will work fine. Is it the best solution? That, only you can decide.