query join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query join help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-02 : 05:07:16

i have a table called customers and a table called transactions

customers has a field called number with a matching field in transactions called number but there can be many transactions per customer

how can I query all customers, (just one time not for each record in transactions) and with a count of all transactions for that number where chargedsuccessfully=1

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 05:14:13

do you mean this:

SELECT C.*, cnt = (SELECT COUNT(*) FROM transactions S WHERE S.number = C.number)
FROM customers C
WHERE chargedsuccessfully = 1;

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-02 : 05:33:53

thanks - i modified to put the and charged successfully=1 in the count as that's the table it's in and it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources