ambiguous column?

[mrsbean]

I originally posted this question to the PHP forum, but I was redirected here. I want to join a table which contains real estate transactions to two other tables. The first table to join my transactions table to contains Agency types. The second table contains a list of real estate agents. I need to join the transaction table to two instances of those two tables - one join for the listing agent, and another join for the selling agent.

I get an error when I try to run the following query. It says that Column AgencyID in the field list is ambiguous. Since I have specified the table which contains AgencyID, I don't understand the problem.

Can anybody tell me how to fix it?

SELECT phtransaction.TransMLS
AS TransactionID, phuser.userFirstName
AS ListingAgentF, phuser.userLastName
AS ListingAgentL, phagency.AgencyType
AS ListingAgentRep, phuser_1.userFirstName
AS SellingAgentF, phuser_1.userLastName
AS SellingAgentL, phagency_1.AgencyType
AS SellingAgentRep, phtransaction.TransListExp, phtransaction.TransCOE,
phtransaction.TransAddress, phtransaction.TransZip
FROM (((phtransaction LEFT JOIN phuser ON phtransaction.TransAgent = phuser.phID)
INNER JOIN phagency
ON phtransaction.TransAgency = phagency.AgencyID)
LEFT JOIN phuser AS phuser_1 ON phtransaction.TransSellingAgent = phuser_1.phID)
INNER JOIN phagency AS phagency_1 ON 
phtransaction.TransSellingAgency = phagency_1.AgencyID
WHERE (((phtransaction.TransMLS)>='20060601')) 
ORDER BY TransMLS DESC LIMIT 0,10

MrsBean

 

[TonyGroves]

I can't see why that happens, but it might help if you were to simplify your query by removing all the brackets; they're not needed.

 

[r937]

problem with that error message is, AgencyID isn't in the field list, so i'm thinking maybe you're running a different query?

in any case, try it like this, my experience is that you should always list your inner joins first, and then the outer joins

SELECT phtransaction.TransMLS  AS TransactionID
     , phuser.userFirstName    AS ListingAgentF
     , phuser.userLastName     AS ListingAgentL
     , phagency.AgencyType     AS ListingAgentRep
     , phuser_1.userFirstName  AS SellingAgentF
     , phuser_1.userLastName   AS SellingAgentL
     , phagency_1.AgencyType   AS SellingAgentRep
     , phtransaction.TransListExp
     , phtransaction.TransCOE
     , phtransaction.TransAddress
     , phtransaction.TransZip
  FROM phtransaction 
INNER 
  JOIN phagency
    ON phagency.AgencyID = phtransaction.TransAgency
INNER 
  JOIN phagency AS phagency_1 
    ON phagency_1.AgencyID = phtransaction.TransSellingAgency
LEFT 
  JOIN phuser 
    ON phuser.phID = phtransaction.TransAgent
LEFT 
  JOIN phuser AS phuser_1 
    ON phuser_1.phID = phtransaction.TransSellingAgent
 WHERE phtransaction.TransMLS >= '20060601'

r937.com | rudy.ca