SQL - SELECT statement with a JOIN

Question

I'm using the Northwind database, and I can't manage to get the following query to work -

select *
from customers
    join orders
        on orders.customerID = customers.customerID
    join [Order Details]
        on orders.OrderID = [Order Details].orderID
    join Products (select Products.productID, Products.ProductName from Products)
        on [Order Details].productID = Products.productID
order by customers.customerID

I get an error saying that there's incorrect syntax near the select in line 7.

What I'm trying to do is that when joining the Products table, it won't bring all the columns but rather just the ProductName and ProductID.

Could somebody please explain what I'm doing wrong? Thanks!

Giannis Paraskevopoulos · Accepted Answer · 2013-09-15 07:42:46Z

2

You first need to specify the subquery and then the alias.

select *
from customers
    join orders
        on orders.customerID = customers.customerID
    join [Order Details]
        on orders.OrderID = [Order Details].orderID
    join (select Products.productID, Products.ProductName from Products) Products 
        on [Order Details].productID = Products.productID
order by customers.customerID

Change

join Products (select Products.productID, Products.ProductName from Products)

to

join (select Products.productID, Products.ProductName from Products) Products

answered Sep 15, 2013 at 7:42

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Tripathi · Accepted Answer · 2013-09-15 07:43:35Z

2

You may want to try this:-

select *
from customers
    join orders
        on orders.customerID = customers.customerID
    join [Order Details]
        on orders.OrderID = [Order Details].orderID
    join (select Products.productID, Products.ProductName from Products) Products
        on [Order Details].productID = Products.productID
order by customers.customerID

answered Sep 15, 2013 at 7:43

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

SQL - SELECT statement with a JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related