0

I was trying to join 3 tables - CurrentProducts, SalesInvoice and SalesInvoiceDetail. SalesInvoiceDetail contains FK/foreign key to the other two tables and some other columns. The first query is ok but the second is not. My question comes at the end of the code.

Right

select *
from CurrentProducts inner join 
(dbo.SalesInvoiceDetail inner join dbo.SalesInvoice
 on dbo.SalesInvoiceDetail.InvoiceID = dbo.SalesInvoice.InvoiceID
)
on dbo.SalesInvoiceDetail.ProductID = dbo.CurrentProducts.ProductID

Wrong

select *
from CurrentProducts inner join 
(select * from
dbo.SalesInvoiceDetail inner join dbo.SalesInvoice
 on dbo.SalesInvoiceDetail.InvoiceID = dbo.SalesInvoice.InvoiceID
)
on dbo.SalesInvoiceDetail.ProductID = dbo.CurrentProducts.ProductID

error - Incorrect syntax near the keyword 'on'.

Why is the second query wrong ? Isn't it conceptually the same as the first one ? That is inside join makes a result set. We select * the result set and then join this result set to CurrentProducts ?

sequel.learner
  • 3,421
  • 7
  • 22
  • 24
  • try add alias to subquery and use it, like this: select * from CurrentProducts inner join (select * from dbo.SalesInvoiceDetail inner join dbo.SalesInvoice on dbo.SalesInvoiceDetail.InvoiceID = dbo.SalesInvoice.InvoiceID ) sid on sid.ProductID = dbo.CurrentProducts.ProductID – Eugene May 13 '13 at 10:28

2 Answers2

1

You need to alias the inner query. Also, in the first one the parentheses are not needed.

select *
from CurrentProducts inner join 
(select * from
dbo.SalesInvoiceDetail inner join dbo.SalesInvoice
 on dbo.SalesInvoiceDetail.InvoiceID = dbo.SalesInvoice.InvoiceID
) A
on A.ProductID = dbo.CurrentProducts.ProductID
kyooryu
  • 1,469
  • 3
  • 23
  • 48
1

The first query is a "plain" join expressed with an older syntax. It can be rewritten as:

select
  *
from
  CurrentProducts
  inner join dbo.SalesInvoiceDetail
             on dbo.SalesInvoiceDetail.ProductID = dbo.CurrentProducts.ProductID
  inner join dbo.SalesInvoice
             on dbo.SalesInvoiceDetail.InvoiceID = dbo.SalesInvoice.InvoiceID

The second query is a join where the second table is a subquery. When you join on a subquery, you must assign an alias to it and use that alias to refer to the columns returned by the subquery:

select
  *
from
  CurrentProducts
  inner join (select *
              from dbo.SalesInvoiceDetail
              inner join dbo.SalesInvoice
                         on SalesInvoiceDetail.InvoiceID = SalesInvoice.InvoiceID
  ) as foo on foo.ProductID = dbo.CurrentProducts.ProductID
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • please show me how to do the aliasing ? i tried the aliasing in given in another answer, but it does not work. – sequel.learner May 13 '13 at 10:30
  • same error - The column 'InvoiceID' was specified multiple times for 'foo'. – sequel.learner May 13 '13 at 10:32
  • 1
    @sequel.learner Don't use `select *`, explicitly list the columns you need from the subquery. It would also appear you don't need a subquery but a plain join. – GSerg May 13 '13 at 10:36
  • Listing the columns is so painful. Wonder why sql is like this. – sequel.learner May 13 '13 at 10:37
  • Selecting all the columns will sooner or later cause more pain :) – kyooryu May 13 '13 at 10:38
  • btw, I ran 1st code and got error - Incorrect syntax near '.'. – sequel.learner May 13 '13 at 10:38
  • @sequel.learner Because when there are two columns with the same name, how are you going to distinguish between the two? Also, you will never see `select *` in good production code because it [causes problems](http://stackoverflow.com/q/3639861/11683). Anyway, I believe you want the plain join here. – GSerg May 13 '13 at 10:40
  • @GSerg - thanks. Btw,can you edit the code so that there is no need to scroll sideways ? – sequel.learner May 13 '13 at 10:43
  • @sequel.learner Ok. I personally don't like this style though. – GSerg May 13 '13 at 10:49