Am I just an idiot?

[WaterTight]

I have two tables: Batch and Orders - both containing BatchID (which is either NULL or a valid id in the orders table). I want to try to avoid adding a StartDate to my Batch table because there is already an OrderDate in my Orders table that contains more accurate information for tracking (specifically, we don't need when the batch was started, we need when the first order was placed).

I would like to return all fields from the Batch table (where customerid is equal to a variable...say '5523' for this example) along with the date the first order was placed. I have the following:

SELECT a.*, b.OrderDate FROM Batch a, Orders b WHERE a.BatchID *= b.BatchID AND a.CustID = 5523

All of this is well and fine except that when I do this, I get duplicate rows for my batches - 1 for each order that was placed.

Any ideas on how I can limit this to just show 1 row for each BatchID?

thanks in advance,
J

 

[JohnDTampaBay]

Add the DISTINCT keyword:

SELECT DISTINCT a.*, b.OrderDate    FROM Batch a, Orders b WHERE a.BatchID *= b.BatchID AND a.CustID = 5523

This will give you one row per order date.

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[foxdev]

along with the date the first order was placed

If I'm reading you correctly, there could be more than one OrderDate, and you only want the first/earliest.

If so, wrap b.OrderDate in a MIN() function. --------------
Low Cost Prints:

http://www.lowcostprints.com

 

[WaterTight]

Doing a distinct would still yield the same results as all the orderdates are different...however, wrapping it in a MIN() function worked like a charm. thanks foxdev - apparently i was just being an idiot...

thanks,
J