joining three tables , error with The multi-part identifier "p.productid" could not be bound

Question

I have tried this question on northwind database: NEED TO KNOW WHERE I DID WRONG. as when I used where statement it worked .

q. List all the products total sales amount from 1997? List the query result by ProductID, ProductName, and Annual Sales Amount for 1997 and sorted it by Annual Sales in Descending order (the annual sales is a calculated column). */

 select p.productid, p.productname , 
            '$'+ CONVERT(varchar(12), sum(od.unitprice*od.quantity*1-od.discount)) as AnnualSales
    from products as p , [Order Details] as od , orders as o
        inner join [Order Details]
    on  p.productid = od.productid
        inner join orders
        on od.orderid = o.orderID
        and o.OrderDate >=  '1997/01/01' and  o.orderdate <= '1997/12/31'
    group by p.productid, p.productname
    order by annualsales desc
    ;

I have got these errors:

        Msg 4104, Level 16, State 1, Line 207
        The multi-part identifier "p.productid" could not be bound.
        Msg 4104, Level 16, State 1, Line 207
        The multi-part identifier "od.productid" could not be bound.
        Msg 4104, Level 16, State 1, Line 209
        The multi-part identifier "od.orderid" could not be bound.

when I tried like this , It worked , please advise where I did wrong:

select p.productid, p.productname , '$'+ CONVERT(varchar(12), sum     (od.unitprice*od.quantity*1-od.discount)) as AnnualSales
from products as p , orders as o , [Order Details] as od
where  p.productid = od.productID
    and o.OrderID = od.OrderID
    and  o.OrderDate >=  '1997/01/01' and  o.orderdate <= '1997/12/31'
group by p.productid, p.productname
order by annualsales desc
;

Scope issue. (Don't mix explicit JOIN and implicit, comma separated joins... Use explicit JOIN everywhere. ) — jarlh, Oct 30 '18 at 07:37
od.unitprice*od.quantity*1-od.discount - What do you expect that expression to produce? Without parentheses, not what you think (most likely). — SMor, Oct 30 '18 at 13:16

Zaynul Abadin Tuhin · Answer 1 · 2018-10-30T07:43:42.937

1

you have to remove extra table name that you used after from [Order Details] as od , orders as o and i think it would be on where condition for o.OrderDate >='1997/01/01' and o.orderdate <='1997/12/31'

select p.productid, p.productname , 
            '$'+ CONVERT(varchar(12), sum(od.unitprice*od.quantity*1-od.discount)) as AnnualSales
    from products as p   
        inner join [Order Details] od
      on  p.productid = od.productid
        inner join orders o
        on od.orderid = o.orderID
        where 
        o.OrderDate >='1997/01/01' and o.orderdate <='1997/12/31'
    group by p.productid, p.productname

    ;

edited Oct 30 '18 at 07:43

answered Oct 30 '18 at 07:37

Zaynul Abadin Tuhin

31,407
5
33
63

thank you so much in solving my problem. – tauqeer Oct 31 '18 at 10:10
@tauqeer if it helps mark it as accepted – Zaynul Abadin Tuhin Oct 31 '18 at 10:12

dwir182 · Answer 2 · 2018-11-01T01:28:10.923

First Do not use Implicit Join..

And for error you mix Implicit Join with comma join and Explicit Join use Join statement.. Why you must not do Implicit Join? Because the evaluation of table and checking condition to specify table in your where Statement and if you forget in where Statement your join become Cross or Cartesian Product. That will be messy in your result.

 select 
      p.productid, p.productname, 
      '$'+ CONVERT(varchar(12), 
      sum(od.unitprice*od.quantity*1-od.discount)) as AnnualSales
 from 
      products as p 
        inner join [Order Details] on  p.productid = od.productid
        inner join orders as o on od.orderid = o.orderID
                             and o.OrderDate >= '1997/01/01' 
                             and o.orderdate <= '1997/12/31'
  group by 
       p.productid, p.productname;

You can read here more about why you must not do Implicit Join Bad habits to kick : using old-style JOINs

Note : I just want to elaborate more info because the op ask about error..

score 0 · Accepted Answer · answered Oct 30 '18 at 13:37

I think you have already got the answer and a good explanation. LEt me explain bit more about the difference of 'Implicit' and 'Explicit' joins.

Implicit Join The "Implicit Join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates:

This is called ANSI SQL-89 join syntax while.

Eg:-

SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID;

Explicit Join

This is called SQL-92 Join. Eg:-

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID

Both syntax are still part of the latest ANSI SQL Standard and in terms of performance or resource there is no difference. Respecting that standard Microsoft has no plan to remove support for SQL 89 syntax in near future.

However, many experts favours ANSI SQL 92 syntax writing SQL Joins in FROM Clause as it is less confusing, because if you omit or forget join condition in the where clause then

SELECT *
FROM employee, department
--WHERE employee.DepartmentID = department.DepartmentID;

Basically, you will be doing a cross join however if you forget to write ON clause in FROM your query will throw an error.

For more details-> Read T-SQL fundaments By Itzik Ben-Gan Chapter 3: There is a whole paragraph about this topic.

If you think that WHERE clause is evaluated after FROM clause so SQL 89 synax can be slower? No, There is something in SQL Server called query optimizer and it is very smart and it has the ability to transform join condition in the where clause before evaluating the query.

Thank you so much in explaining me about implicit and explicit syntax, good learning curve for me, and I will definitely follow the explicit syntax. Thank you so much in solving my problem and also to understand the correct syntax. — tauqeer, Oct 31 '18 at 10:08

joining three tables , error with The multi-part identifier "p.productid" could not be bound

3 Answers3