The multi part identifier could not be bound

Question

SELECT * FROM Products_Joined, Products 
WHERE p.ProductManufacturer = 'Sony' 
ORDER BY p.ProductCode

I keep getting the error The multi part identifier p.ProductManufacturer could not be bound

I tried:

Setting the Order By
Adding the PRODUCTS table to the FROM

Is there something I'm missing?

Where is `p` defined in that query? (hint: it isn't) Also you are missing a join condition. — Martin Smith, Mar 19 '12 at 14:26
Can you please clarify what you are trying to accomplish with this query? For example, desired output results. — ImGreg, Mar 19 '12 at 14:28
The `p` it never worked when I used Products.ProductManufacturer — henryaaron, Mar 19 '12 at 14:28

score 3 · Answer 1 · answered Mar 19 '12 at 14:28

3

You should use:

SELECT p.*, pj.*
FROM dbo.Products p
INNER JOIN dbo.ProductsJoined pj ON ..... <== add your missing JOIN condition here
WHERE p.ProductManufacturer = 'Sony' 
ORDER BY p.ProductCode

First of all: never use SELECT * in your production code.

Secondly: use the proper ANSI JOIN syntax (INNER JOIN..) to clearly show what you're joining, and on what JOIN condition (which is missing in your case - you're producing a cartesian product here.....)

Third: if you use table aliases like p. - you need to define them, too!

answered Mar 19 '12 at 14:28

marc_s

732,580
175
1,330
1,459

"never use SELECT * in your production code" -- "always be wary of anyone telling you to never do something in SQL. [In the following examples](http://stackoverflow.com/a/9328000/15354), SELECT * can do no harm and arguably has benefit as regards readability and code maintenance...". – onedaywhen Mar 21 '12 at 11:05
@onedaywhen: those are rather rare edge cases - and even then - there's no **benefit** in using `SELECT *` - you could just as well type out the columns in question - which I always recommend doing – marc_s Mar 21 '12 at 12:31
The examples are simplified but are common in their general form. The benefit is you don't have to type them out again (DRY), which reduces the error of getting it wrong, especially when later adding columns. But forcing users to always type out the comma list is very strict. Speaking of strict: the result of a Cartesian product is a set of ordered pairs, not a relation/table. The OP's syntax is not only ANSI SQL but it is ISO SQL too. The literature uses the term 'range variable' for what the SQL standard calls a 'correlation name' for what you call an 'alias' ;) – onedaywhen Mar 21 '12 at 13:16

Justin Pihony · Accepted Answer · 2012-03-19T14:34:46.647

You have no p object. You need to alias one of your tables.

SELECT * FROM Products_Joined, Products AS p
WHERE p.ProductManufacturer = 'Sony' 
ORDER BY p.ProductCode

That will fix your immediate problem, however you should have a JOIN on your tables or else you are doing a CROSS JOIN, which is usually not preferable. An example of what it would look like is below.

SELECT * 
FROM Products_Joined
    JOIN Products AS p
        ON Products_Joined.ProductsID = p.ProductsID 
--This join is a guess on what the common column is between these two tables
--Change as necessary
WHERE p.ProductManufacturer = 'Sony' 
ORDER BY p.ProductCode

UPDATE BASED ON YOUR COMMENT

If you received the error even with a Products.ProductManufacturer, then you are probably missing the ProductManufacturer column in the Products table. I would check your schema and verify the column exists.

@DenisValeev Yes, I noted that. I was not going to rewrite the query for him, because I did not know what to join on, so just left it without code. I have updated it with a sample using a guess — Justin Pihony, Mar 19 '12 at 14:36

The multi part identifier could not be bound

2 Answers2