I used Northwind database and test with following query :
SELECT *
FROM products
JOIN suppliers ON suppliers.supplierID = products.supplierID
and I got a red message like this :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "products.supplierID" could not be bound.
Can anyone shed some light ? many thanks,
SELECT suppliers.supplierID,products.supplierID
FROM products
JOIN suppliers ON suppliers.supplierID = products.supplierID
You should have to explicitly say which supplierid you need to display in results..put the columns on conflict (mandatory) on select statement
* cannot be an issue in this case (certainly not the reason behind the error in question).
My only guess based on this error and your query is that there is no SupplierId in the Products table. I would check the schema first.
I've just run the nortwind db script and ran the query and it runs fine for me.
SELECT *
FROM products
INNER JOIN suppliers ON suppliers.supplierID = products.supplierID
Have a look at the products table, is the supplierId there? if so then make sure you are running your query against the right database.
can you run the below queries and are they running successfully? The third query is essentially a join in another way.
select SupplierID from Suppliers
go
select SupplierID from Products
go
select * from Products p , Suppliers s
where p.SupplierID = s.SupplierID
Instead Of SELECT * Write the appropriate columns you want to select from both table
like SELECT suppliers.supplierID,suppliers.name..etc
both tables contain supplierID that is why the error comes.
Check the collation settings of the database and if case sensitive check that you are using case sensitive data object names.
your code is creating amibiguity in selecting the columns in select list by "select * " it is not getting which table columsn need to be selected as there are two tables used after from clause..do one thing aliase tables and use aliased column names in select list instead of simple *
eg.
select a.* from test a inner join xyz b on a.id=b.id
