CODEWITHSUNDEEP
sql
thomas
thomas

Reputation: 1174

SQL - SELECT statement with a JOIN

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!

Upvotes: 2

Views: 180

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

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

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

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

Upvotes: 2

Related Questions