SQL Server 2005 INNER JOIN

Question

I have two tables that are grouped by OrderID.

Something like:

Orders.OrderID
OrderDetails.OrderID

I'm trying to call a column from the OrderDetails table, but I need it in the Orders table.

I need to call a JOIN or INNER JOIN or GROUP BY where I can group the OrderDetails with the Orders table and where the OrderID's match display the OrderDetails.ProductCode. Since that barely makes sense, here is my current query:

SELECT 
    Orders.OrderID, Orders.OrderDate, Orders.ShipLastName, Orders.ShipFirstName, 
    Orders.ShipCity, Orders.ShipState, Orders.Order_Comments, Orders.OrderNotes, 
    Orders.ShipPhoneNumber, Orders.ShipDate
FROM Orders 
WHERE Orders.OrderStatus = 'Shipped'  
AND Orders.ShipDate > DATEADD(Day, Datediff(Day,0, GetDate() -20), 0)
AND Orders.ShipDate < DATEADD(Day, Datediff(Day,0, GetDate() -13), 0)

Basically I'd like to SELECT OrderDetails.ProductCode but first need to group them by OrderID since they're on different tables.

Thanks

What do you mean by *ProductCode but first need to group them by OrderID* ..... this is not clear at all. If you have an `Order` with 5 details, and those refers to five different `ProductID` values - you cannot group by - these are distinct, separate values - you'll always get multiple lines! — marc_s, Dec 27 '11 at 07:39
Sounds like you want to concatenate the details and return them as a (comma-separated?) list, in which case you could start [from here](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 "Simulating group_concat MySQL function in MS SQL Server 2005?"). — Andriy M, Dec 27 '11 at 12:32

score 0 · Answer 1 · answered Dec 27 '11 at 00:46

Try

SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipLastName, Orders.ShipFirstName, Orders.ShipCity, Orders.ShipState, Orders.Order_Comments, Orders.OrderNotes, Orders.ShipPhoneNumber, Orders.ShipDate, OrderDetails.[COLUMNNAME_HERE]
FROM Orders, OrderDetails
WHERE Orders.OrderID = OrderDetails.OrderID AND 
Orders.OrderStatus = 'Shipped' AND
AND Orders.ShipDate > DATEADD(Day, Datediff(Day,0, GetDate() -20), 0)
AND Orders.ShipDate < DATEADD(Day, Datediff(Day,0, GetDate() -13), 0)

It's saying OrderDetails.OrderID could not be bound. I think I should mention, multiple rows in the OrderDetails table can have the same OrderID — henryaaron, Dec 27 '11 at 00:50

score 0 · Answer 2 · answered Dec 27 '11 at 01:08

0

Try this:

SELECT o.OrderID, o.OrderDate, o.ShipLastName, o.ShipFirstName,
       o.ShipCity, o.ShipState, o.Order_Comments, o.OrderNotes,
       o.ShipPhoneNumber, o.ShipDate
FROM OrderDetails od
INNER JOIN Orders o ON o.OrderID = od.OrderID
WHERE o.OrderStatus = 'Shipped'  
AND o.ShipDate > DATEADD(Day, Datediff(Day, 0, GetDate() -20), 0)
AND o.ShipDate < DATEADD(Day, Datediff(Day, 0, GetDate() -13), 0)

Then you can add whatever details you need from the OrderDetails table to the list of columns (otherwise you will see identical rows for each unique OrderID in OrderDetails).

answered Dec 27 '11 at 01:08

RickNZ

18,448
3
51
66

This worked well, but it made a duplicate for each order that had multiple OrderDetails that matched it based on the OrderID. Multiple rows in the OrderDetails table can have the same OrderID. How can I have it separate the ProductCodes with commas and not duplicate the Order? – henryaaron Dec 27 '11 at 01:19
Yes, as I said, it would have duplicate rows. That's a natural consequence of the fact that OrderDetails has multiple rows with the same OrderID. You might add od.ProductCode to each row to make them unique. Separate the ProductCodes with commas? That's an entirely different question than how to do an inner join. – RickNZ Dec 27 '11 at 02:17
Is it easy to separate the Product Code with anything? Spaces, Commas, Periods? – henryaaron Dec 27 '11 at 02:22

score 0 · Accepted Answer · answered Mar 05 '12 at 01:12

0

Thanks to Andriy M I found this and it worked!

answered Mar 05 '12 at 01:12

henryaaron

6,042
20
61
80

SQL Server 2005 INNER JOIN

3 Answers3