Join tables or something similar? 2

[ifx]

Hi all,

I've been using two recordsets in ASP to do this, but I was hoping a good SQL statement could do it all (and wont cause a timeout like this does sometimes!

OK, here's the setup: I've got two tables: CustomerDetails and Orders. I need to retrieve some statistics on the orders, eg how many orders on a specified day.

Each customer entry in the CustomerDetails table has a CustID and purchase date. For every custID there's one or more orders in the Orders table.

CustomerDetails table eg:
[tt]
CustID purchaseDate name
------ ------------ ----
1 10/10/03 Bill Gates
2 10/10/03 Joe Bloggs
[/tt]
Orders table:
[tt]
ID custID prodID
-- ------ ------
1 1 112365
2 1 556845
3 1 556986
4 2 569986
5 2 125522
[/tt]
I just need the Count(ID) from Orders by specifying a purchaseDate for CustomerDetails. I'm sure to most of you this'll seem really simple, but SQL isn't my strong point when it comes to joins and inner loops etc!!

Thanks loads for your time!!

 

[JamesLean]

SELECT COUNT(*)
FROM orders o JOIN customerdetails c ON o.custid = c.custid
WHERE c.purchasedate = '20031015'

--James

 

[mihaildamjan]

Try this

select count(o.*) from Orders o
inner join CustomerDetils c
on o.CustID = c.CustID
where c.PurchaseDate = 'somedate'

 

[ifx]

Fantastic!! USed JamesLean's code which worked perfectly, and way faster!

Thansk a lot, have a star each!