Query not bringing back correct results

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 2000 Forums

Transact-SQL (2000)

Query not bringing back correct results

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-12-07 : 13:27:44

This query is supposed to do the following:

1) Bring back records from master where

a) There are no related records in debtorcreditcards based on m.number = b.number
b) There are no related records in pdc based on m.number = c.number
c) There are more than 2 records in payhistory based on m.number = ph.number

so far, it produces results, but not the right results. It's bringing back results and basically ignoring the requirement that there must be at least 2 payments in the payhistory table for each record in the master table.

THE QUERY:
--------------

SELECT m.number,
m.branch,
m.desk,
m.status,
m.qlevel,
PayCount,
d.number

FROM dbo.Master m
INNER JOIN status s ON s.code = m.status
INNER JOIN desk de ON de.code = m.desk
LEFT OUTER JOIN debtorcreditcards as b
ON m.number = b.number
LEFT OUTER JOIN pdc as c
ON m.number = c.number
LEFT OUTER JOIN
(
SELECT number, count(*) as PayCount
FROM payhistory ph
WHERE batchtype = 'PUR'
AND paymethod IN ('ACH Debit','Check','Credit Card')
AND ph.entered < dateadd(dd, -35, GetDate())
GROUP BY number
HAVING count(*) > 2

) as d
ON m.number = d.number
WHERE m.customer IN (2,150,151,101,129,108,116,120,117)
AND m.desk NOT IN ('C0159','C0131','C0172','C0181','00001')
AND de.branch = '00001'
AND s.statustype = '0 - ACTIVE'
AND b.number is null -- Ensures there are no records in debitcreditcards
AND c.number is null -- Ensures there are not records in pdc
AND d.number is null -- Brings back records that satisfies the LEFT OUTER JOIN for payhitory critieria

AND IT'S DRIVING ME BATTY!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-07 : 16:15:20

>>There are more than 2 records in payhistory based on m.number = ph.number
to accomplish this...

I think you want to:
1. change the join to the derived table (as d) to an INNER JOIN
2. remove: "AND d.number is null"

Be One with the Optimizer
TG

dba123
Yak Posting Veteran

90 Posts

Posted - 2005-12-07 : 19:09:56

Thanks for the input. Actually that wasn't it but I resolved it earlier today.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources