December 3, 2007 at 5:00 am
Hi all,
I have a problem on outputting orders with multiple products. For example, one order can contain a number of entries in an "OrderDetails" table, but my script below only ever outputs one of the products. How do I get it to output the items in the select line until each line of the "OrderDetails" table has been output?
SELECT
orders.orderid, orders.orderdate, orderdetails.productid, laborderstatus.initialduedate, products.labsoutputproduct, products.labsoutputtint
FROM
orders left join orderdetails inner join products ON orderdetails.productid = products.productid
ON orders.orderid = orderdetails.Orderid
left join sku on orders.framestylecode = sku.sku
left join laborderstatus on orders.orderid = laborderstatus.labref
WHERE orders.labid is null AND products.labsoutputproduct is not null AND orders.ordercancel <> 'Y'
ORDER BY orders.orderdate
December 3, 2007 at 5:08 am
Provided each order detail has an entry in the products table, this will work.
SELECT
orders.orderid
,orders.orderdate
,orderdetails.productid
,laborderstatus.initialduedate
,products.labsoutputproduct
,products.labsoutputtint
FROM
orders
left join orderdetails
ON orders.orderid = orderdetails.Orderid
inner join products
ON orderdetails.productid = products.productid
left join sku
ON orders.framestylecode = sku.sku
left join laborderstatus
ON orders.orderid = laborderstatus.labref
WHERE
orders.labid is null
AND products.labsoutputproduct is not null
AND orders.ordercancel <> 'Y'
ORDER BY
orders.orderdate
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 6:33 am
Thanks for the reply, but the problem still exists. For example, one of the orders that appear in the result should have 2 outputs (as shown below)
select orderdetails.orderid, orderdetails.productid, orderdetails.qty, products.description, products.labsoutputproduct, products.labsoutputtint
from
orderdetails inner join products
on orderdetails.productid = products.productid
where orderdetails.orderid = '5135013709'
Gives following result
513501370950310216147661Re-Glaze Frame - StandardNULLNULL
513501370900154145-31MAR CoatingNULLAOM6B
5135013709PD281BifocalCD28NULL
At present, using the first script, I would only get the 3rd line (i.e. PD28, Bifocal, CD28)
December 3, 2007 at 6:36 am
Nadeem Shafi (12/3/2007)
Thanks for the reply, but the problem still exists. For example, one of the orders that appear in the result should have 2 outputs (as shown below)select orderdetails.orderid, orderdetails.productid, orderdetails.qty, products.description, products.labsoutputproduct, products.labsoutputtint
from
orderdetails inner join products
on orderdetails.productid = products.productid
where orderdetails.orderid = '5135013709'
Gives following result
513501370950310216147661Re-Glaze Frame - StandardNULLNULL
513501370900154145-31MAR CoatingNULLAOM6B
5135013709PD281BifocalCD28NULL
At present, using the first script, I would only get the 3rd line (i.e. PD28, Bifocal, CD28)
It looks like it's the condition in the WHERE clause that's checking on "labsoutputproduct" being NOT NULL. In your above example, it IS null. ???
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 6:48 am
Jason - thanks for your help, I couldn't see the obvious!
If changed the WHERE clause to
(products.labsoutputproduct is not null or products.labsoutputtint is not null)
and it works!
🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply