CODEWITHSUNDEEP
sqlsql-server
rottenbanana
rottenbanana

Reputation: 209

Multi part identifier could not be found

I have this query that is throwing.

SELECT 
    Firm._Name, Broker._name
FROM 
    Firm, Broker
INNER JOIN 
    Employer AS emp ON emp.firmid = Firm.firmid 
INNER JOIN 
    emp ON emp.brokerid = Broker.brokerid
WHERE 
    emp._name = 'xxx';

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Firm.firmid" could not be bound.

Firm table:

firmid
_Name

Broker Table:

brokerid
firmid
_name

Employer Table

employerid
brokerid
firmid
_name

Thanks!

Upvotes: 0

Views: 835

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

You are cross-joining Firm and Broker first, i.e. combine every Firm record with every Broker record. This is certainly not what you want. Don't use commas to join. Always use explicit joins. (So if you really wanted to cross join the two, you would say FROM Firm CROSS JOIN Broker).

Then you join Employer to Firm and call it emp. You also join the table emp to Broker, but that table probably doesn't exist. I guess you meant to use the alias just created, but this is not possible. And you don't even want to join another record. You simply want to join Employer with both Firm and Broker.

So: Get the records from Employer, then join Firm and Broker. (At least this is what I guess you want.)

select f._name, b._name
from employer e
inner join firm f on f.firmid = e.firmid
inner join broker b on b.brokerid = e.brokerid
where e._name = 'xxx';

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

    SELECT Firm._Name, Broker._name
    FROM Firm
    INNER JOIN Employer emp
    ON emp.firmid = Firm.firmid 
    INNER JOIN BROKER 
    ON emp.brokerid = Broker.brokerid
    WHERE emp._name = 'xxx';

Correct the syntax as above.

Upvotes: 1

Related Questions