not sure the best approach to this problem....
-
OK, I have 2 tables one has product information grouped by a sales order id. another table has a list of serial numbers related to the items pulled out of stock to fill that order. What I want to do is run a query/subquery to provide a single resultset that includes the info for the products on a sales order and a comma seperated list of serial numbers of the producted that was pulled. for example i would like my result set to look something like this.... result ==========
order_id prod_id qty sn unit_price
100123 12345 2 abcd123,abcd124 2.99
100123 12346 1 bbcd123 1.99any suggestion on how to do this on one query...? Thanks! Chris
-
OK, I have 2 tables one has product information grouped by a sales order id. another table has a list of serial numbers related to the items pulled out of stock to fill that order. What I want to do is run a query/subquery to provide a single resultset that includes the info for the products on a sales order and a comma seperated list of serial numbers of the producted that was pulled. for example i would like my result set to look something like this.... result ==========
order_id prod_id qty sn unit_price
100123 12345 2 abcd123,abcd124 2.99
100123 12346 1 bbcd123 1.99any suggestion on how to do this on one query...? Thanks! Chris
nm, I figured it out the query I used is.... SELECT t1.ProductID, t1.ListNum, t1.Quantity AS Qty, t1.UnitPrice, (SELECT GROUP_CONCAT( sn SEPARATOR ', ' ) FROM sn_table AS t2 WHERE t1.listnum = t2.listnum GROUP BY listnum ) AS SN FROM ProductList AS t1 WHERE t1.OrderID = 'ORD000023' works like a charm!