The multi-part identifier could not be bound

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

The multi-part identifier could not be bound

Author

Topic

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-18 : 08:21:05

Hi,

Just signed up, so hello to all

I have been using oracle for the past 2 years and so have gotten a bit rusty with my sql lately.

I have a slight problem with the following SQL, I receive the error "The multi-part identifier "Supplier.SupplierName" could not be bound."

Here is my sql query, any help would be much appreciated.

SELECT Distinct Contract.*
FROM ContractSupplier
INNER JOIN Contract ON ContractSupplier.ContractID = Contract.ID
INNER JOIN Supplier ON ContractSupplier.SupplierID = Supplier.ID
LEFT OUTER JOIN Department
RIGHT OUTER JOIN Officer ON Department.ID = Officer.DepartmentID ON Contract.ContractManager = Officer.ID
WHERE Contract.ID IS NOT NULL AND Supplier.SupplierName LIKE '%gas%'

ORDER BY Contract.LongReference, Contract.Description

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 08:29:11

RIGHT OUTER JOIN Officer ON Department.ID = Officer.DepartmentID here is something wrong ON Contract.ContractManager = Officer.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 08:32:02

Hi. Welcome to sqlteam.

I think your sql is a little malformed. ( the part around the LEFT OUTER JOIN deparment RIGHT OUTER JOIN Officer)

Was it supposed to be this?


SELECT	Distinct
	Contract.*
FROM
	ContractSupplier 
	INNER JOIN Contract ON ContractSupplier.ContractID = Contract.ID 
	INNER JOIN Supplier ON ContractSupplier.SupplierID = Supplier.ID 

	RIGHT OUTER JOIN Officer ON Contract.ContractManager = Officer.ID

	LEFT OUTER JOIN Department ON Department.ID = Officer.DepartmentID 

WHERE
	Contract.ID IS NOT NULL
	AND Supplier.SupplierName LIKE '%gas%'

Interesting way to join the tables. Not sure what you are trying to do as I don't know your schema.

I'm wondering if your strange ON conditions were getting evaluated out of sequence.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-18 : 08:44:46

Thank you for all your quick replies.

here is an upload of my schema: https://doc-08-bk-docs.googleusercontent.com/docs/secure/n3sgmfnv3f69hu8raa1fv54cmh2o5dd1/f6re58b8baj3cf1edjo5v6ifeu2fqnqn/1266494400000/11555526544365896477/11555526544365896477/0B8PDLUa_cQqJYmMzOTRlYjQtM2JkOS00MTU2LTg1NWItMzk0ZWE1NzBiZDBj?nonce=huuc615ophn1o&user=11555526544365896477&hash=sv6pcneu8pqe8hbblp4efql484dcsgpk

Hope this clears up what i am trying to do. Basically I want to return all columns from contract table whilst being able to search by supplier name... its quite hard to explain, hope you understand.

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-18 : 08:51:39

Maybe I should start from the beginning, this code exists on an SQL stored procedure of mine which I am using to run through .NET Here is my stored prodedure below.

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[GetAdvancedSearchContractList]
(
@Where varchar(1000)
)
AS

DECLARE @SQL varchar(8000)

SELECT @SQL =

'SELECT Distinct Contract.*
FROM ContractSupplier RIGHT OUTER JOIN
Contract ON ContractSupplier.ContractID = Contract.ID LEFT OUTER JOIN
Supplier ON ContractSupplier.SupplierID = Supplier.ID RIGHT OUTER JOIN
Officer ON Contract.ContractManager = Officer.ID LEFT OUTER JOIN
Department ON Department.ID = Officer.DepartmentID
WHERE Contract.ID IS NOT NULL' + @Where + ' ORDER BY Contract.LongReference, Contract.Description'

EXEC(@SQL)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 08:53:15

Hi.

Sadly my company's firewall blocks google docs -- it counts them as personal storage.

Does this do what you want though -- (this is a guess based on your earlier sql)


SELECT	Distinct
	Contract.*
FROM
	ContractSupplier 
	INNER JOIN Contract ON ContractSupplier.ContractID = Contract.ID 
	INNER JOIN Supplier ON ContractSupplier.SupplierID = Supplier.ID 
	LEFT OUTER JOIN Officer ON Contract.ContractManager = Officer.ID
	LEFT OUTER JOIN Department ON Department.ID = Officer.DepartmentID 
WHERE
	Supplier.SupplierName LIKE '%gas%'

Removed the contract.ID NOT NULL part as it was implicit in the INNER JOIN

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 08:56:15

and how will you be executing it?
Anyways as a debugging step replace EXEC by PRINT and post the sql string it builds

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 08:59:29

Is it this? I'm stabbing in the dark here!


SELECT DISTINCT
	c.*
FROM
	contract c
	LEFT JOIN contractSupplier cs ON cs.[contractID] = c.[ID]
	LEFT JOIN officer o ON o.[ID] = c.[contractManager]
	LEFT JOIN deparment d ON d.[ID] = o.[departmentID]

	LEFT JOIN supplier s ON
		s.[ID] = cs.[supplierID]
		AND s.[SupplierName] LIKE '%gas%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-18 : 09:50:12

no thats not it Charlie, this is driving me a little insane I dont see what I am doing wrong here.

SELECT Distinct Contract.*
FROM Contract LEFT OUTER JOIN
Supplier ON Contract.ID = Supplier.ID RIGHT OUTER JOIN
Officer ON Contract.ContractManager = Officer.ID LEFT OUTER JOIN
Department ON Department.ID = Officer.DepartmentID
WHERE Contract.ID IS NOT NULL AND Supplier.SupplierName Like '%gas%' ORDER BY Contract.LongReference, Contract.Description

Does it have anything to do with SupplierName not being in the select clause, hence its having trouble actually using it in its where clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 09:52:49

quote:
Originally posted by ggeorgiou

no thats not it Charlie, this is driving me a little insane I dont see what I am doing wrong here.

SELECT Distinct Contract.*
FROM Contract LEFT OUTER JOIN
Supplier ON Contract.ID = Supplier.ID RIGHT OUTER JOIN
Officer ON Contract.ContractManager = Officer.ID LEFT OUTER JOIN
Department ON Department.ID = Officer.DepartmentID
WHERE Contract.ID IS NOT NULL AND Supplier.SupplierName Like '%gas%' ORDER BY Contract.LongReference, Contract.Description

Does it have anything to do with SupplierName not being in the select clause, hence its having trouble actually using it in its where clause

Just in case you've not seen it I'll repeat my question.
Are you sure this is actual query which is getting executed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-18 : 09:55:47

Apologies visakh16 - I didn't see your msg.

Yes I am sure this is the query which is getting ececuted as I can see from my .Net debugger.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-18 : 10:21:57

OK -- go back to basics.

Can you give us:

The table DML (structure)

sample data for each table

current output

desired output

then we'll forget about the query you've got at the moment and make a new one.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ggeorgiou
Starting Member

7 Posts

Posted - 2010-02-19 : 06:48:59

I am so sorry for wasting your time, I was working on the live system and not the test system... shucks! Luckily I never saved any data.

Guess you can tell, that I havent used .NET for the past 2 years huh!

Sorry once again and thank you for all your replys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 07:07:38

quote:
Originally posted by ggeorgiou

I am so sorry for wasting your time, I was working on the live system and not the test system... shucks! Luckily I never saved any data.

Guess you can tell, that I havent used .NET for the past 2 years huh!

Sorry once again and thank you for all your replys

So? you still didnt tell us why you got error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources