January 19, 2011 at 10:49 am
Hi i have this script so far
Select Orders.OrderId,Orders.OrderNotes,OrderDetails.ProductCode,OrderDetails.Productname,OrderDetails,Quantity
From Orders,Orderdetails
Where Orders.Orderstatus='Pending'
OR Orders.Orderstatus= 'Processing'
OR Orders.Orderstatus='New'
I need to Select the OrderID and OrderNotes from Orders but also need the ProductCode, ProductName and Quantity from the order details table.
But i only need the orders that are Pending Processing or New.. Ive been at this for two days and i cant figure it out would i have to use a union? cause that didnt work. Please Help Me
January 19, 2011 at 10:54 am
Justin 17041 (1/19/2011)
But i only need the orders that are Pending Processing or New.. Ive been at this for two days and i cant figure it out would i have to use a union? cause that didnt work. Please Help Me
Hm, what you have there looks like it should work at first glance, other than you repeating data because of your CROSS JOIN (note the comma with no connection between the two tables). Please take a look at the first link in my sig to help show you how to post DDL and testable data, and we can probably help you clean up your issue once you've done that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2011 at 11:02 am
Table 1
OrderDetails
orderdetailidorderidproductcodeproductname quantity
5 1002 AIRVALV-ARP-21/2" Brass Electronic Valve 15mm Orifice - 200psi4
6 1003 SWITCH-14Pressure Switch 0-200psi Adjustable 1
7 1004 AIRVALV-ARP-21/2" Brass Electronic Valve 15mm Orifice - 200psi0
8 1005 5500-6550-5-6NO5-Lug 5.00" To 6-Lug 5.50" X 2.00" 4
Table 2
Orders
Orderid OrderStatus OrderNotes
1123 Pending Name or make of car
1221 Processing ECT
1231 New ECT
January 19, 2011 at 11:03 am
Looks like you need to JOIN orders and OrderDetails. Something like this:
Select *
From Orders
JOIN Orders on OrderDetails.OrderID = Orders.OrderID
WHERE Orders.OrderStatus in ('Pending', 'Processing', 'New')
January 19, 2011 at 11:10 am
Posting the table definitions, sample data and expected results will help get you a tested answer.
What you're lacking here is the join between the order and the detail table so the query knows how to match the orders and the details. Something like:
Select Orders.OrderId,Orders.OrderNotes,OrderDetails.ProductCode,OrderDetails.Productname,OrderDetails,Quantity
From Orders join Orderdetails on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
January 19, 2011 at 11:11 am
Instead of selecting all is there away just to bring back certain columns instead of them all because this is a lot.
P.S thank you all for you help so far 🙂
January 19, 2011 at 11:15 am
Select
Orders.OrderId,
Orders.OrderNotes,
OrderDetails.ProductCode,
OrderDetails.Productname,
OrderDetails.Quantity --this is where your typo was. 'orderdetails,quantity' instead of 'orderdetails.quantity'
From Orders
join Orderdetails
on orders.orderid = orderdetails.orderid
Where Orders.Orderstatus in ('Pending', 'Processing', 'New')
you're getting all columns because you have a typo (?) in the select.
January 19, 2011 at 11:17 am
When i use the second join one it said orderdetails is not a valid column name =(
January 19, 2011 at 11:39 am
Justin 17041 (1/19/2011)
When i use the second join one it said orderdetails is not a valid column name =(
Justin, what you posted above isn't consumable into a test. Do please read the link, it will explain how and why to do it. If you can't provide us with an immediately consumable and testable structure, you will rarely get tested and functional code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2011 at 11:48 am
THANK YOU CALVO!!!!!!!!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply