join 2 table problem - 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

General SQL Server Forums

New to SQL Server Programming

join 2 table problem

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-12-09 : 19:27:56

I have 3 tables and i need to do a group by on

1. orders
2. paymentscredit
3. paymentscreditfailed

i need a field creditcardtype out of the paymentscredit and paymentscreditfailed
in order to get the info i need

what i want is to have

total orders, failed, completed

cant figure out how to get the group by when joining on tables

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-09 : 22:58:15

It will be easy for us to understand your requirement if you can provide the table structure and relation between then.

Do also provide some sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-11 : 00:57:00

something like this (i have assumed columnnames here so you need to replace them with actual ones to get working solution)


SELECT o.orderid,
COUNT(*) AS totalorders,
COUNT(CASE WHEN f.orderid IS NOT NULL THEN 1 ELSE NULL END) AS failed,
COUNT(CASE WHEN f.orderid IS NULL THEN 1 ELSE NULL END) AS completed
FROM orders o
INNER JOIN paymentcredit c
ON c.ordeid=o.orderid
LEFT JOIN paymentscreditfailed f
on f.orderid = o.orderid
GROUP BY o.orderid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources