[thelist] QUICK SQL Sub Query [SOLVED]

[Rob Smith]

This is the correct query (w/subquery):

SELECT DISTINCT lexjet_purchase_receipt.purchase_num,
lexjet_purchase_receipt.ship_to_name,
lexjet_purchase_receipt.ship_to_state,
lexjet_purchase_receipt_lineitem_1.vendor_sku,
lexjet_purchase_receipt_lineitem_1.list_price,
lexjet_purchase_receipt_lineitem_1.quantity,
lexjet_purchase_receipt.date_created,
lexjet_purchase_receipt_lineitem_1.shipping_method,
lexjet_purchase_receipt_lineitem_1.ship_via,
lexjet_purchase_receipt.ship_to_zip,
lexjet_purchase_receipt.ship_to_street,
lexjet_purchase_receipt.ship_to_street2,
lexjet_purchase_receipt.ship_to_street3,
lexjet_purchase_receipt.ship_to_city, (SELECT     COUNT(DISTINCT
ship_via) AS Expr1 FROM  lexjet_purchase_receipt_lineitem  WHERE
(purchase_num = lexjet_purchase_receipt.purchase_num)) AS counts FROM
lexjet_purchase_receipt INNER JOIN lexjet_vendor ON
lexjet_purchase_receipt.shopper_id = lexjet_vendor.shopper_id INNER JOIN
lexjet_purchase_receipt_lineitem AS lexjet_purchase_receipt_lineitem_1
ON  lexjet_purchase_receipt.purchase_id =
lexjet_purchase_receipt_lineitem_1.purchase_id WHERE
(lexjet_vendor.shopper_id = 'SE99DEMQGES92LP60Q0781AF7RNEF62C') AND
(lexjet_purchase_receipt.date_created BETWEEN '4/12/2006' AND
'4/15/2006') ORDER BY lexjet_purchase_receipt.purchase_num DESC

 

Thanks anyway,


Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236