Retrieve records with duplicate values in more than 1 column

[ccshadow]

I'm attempting to retrieve values joined from 6 different tables and further filtered based upon values in 2 different columns using WHERE IN. The statements worked independently as expected, but when I combine them into one I get an error about the number of arguments in my final SELECT. I understand why I'm getting it, but it seems there should be some way to accomplish the task. I need to select records where the the account occurs only once for a specific lob. Can someone tell me what I need to do to tweak this out?

SELECT mr.tDate, mr.account, mr.lob, mr.subreq, mr.reason, mr.inPkg, mr.outPkg, dip.inPkgStatus, dop.outPkgStatus, dr.reasonStatus, mr.comp, mr.comments, mr.change, au.dwn AS auditInPkg
INTO #once
FROM tbl_masterRetention mr
INNER JOIN tbl_dropInPkg dip ON mr.inPkg = dip.inPkgCode
INNER JOIN tbl_dropOutPkg dop ON mr.outPkg = dop.outPkgCode
INNER JOIN tbl_dropReasons dr ON mr.reason = dr.reasonCode
LEFT JOIN tbl_audit au ON au.inPkg = mr.inPkg AND au.outPkg = mr.outPkg
WHERE mr.account IN
(SELECT account, lob FROM tbl_masterRetention GROUP BY account, lob HAVING COUNT(account) = 1 AND COUNT(lob) = 1)

 

[bborissov]

WHERE  mr.account IN
(SELECT account, lob FROM tbl_masterRetention GROUP BY account, lob HAVING COUNT(*) = 1)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ccshadow]

I get the same error as when I specify both account and lob as COUNT args - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

 

[r937]

where the account occurs only once for a specific lob.

WHERE ( SELECT count(lob)
          FROM tbl_masterRetention 
         where account =  mr.account ) = 1

r937.com | rudy.ca

 

[ccshadow]

Unfortunately that's not working.

There can be one of three different lob's tracked for an account entry - 'vid', 'cdv', 'hsi'. The above suggested code only grabs account entrys that have one lob tracked. For instance if an account only has been tracked for 'vid', the above code retrieves it. I need it to grab all lobs for an account that have been tracked once. For example, an account could have been tracked once (or have one record) for the 'vid' and 'cdv' lob's and twice (or 2 records) for 'hsi', and the code needs to grab the 'vid' and 'cdv' entries.

 

[r937]

okay, try this --

WHERE ( SELECT 937
          FROM tbl_masterRetention 
         where account =  mr.account 
        group
            by lob
        having count(*) = 1 ) is not null

r937.com | rudy.ca

 

[ccshadow]

(select lob from tbl_masterRetention where account = account group by lob having count(*) = 1)

Tried the above and it retrieves nothing.

 

[r937]

sorry, change IS NOT NULL to IS NULL

r937.com | rudy.ca

 

[ccshadow]

Wish I could say that worked, but it's still grabbing duplicate lobs for account entries.

 

[ccshadow]

I doubt anyone else would be helped by this info, but someone successfully assisted me with this problem. See code below.


SELECT mr.tDate, mr.account, mr.lob, mr.subreq, mr.reason, mr.inPkg,
mr.outPkg, dip.inPkgStatus, dop.outPkgStatus, dr.reasonStatus,
mr.comp, mr.comments, mr.change, au.dwn AS auditInPkg
INTO #once
FROM tbl_masterRetention mr
JOIN (select account, lob
from tbl_masterRetention
group by account, lob
having count(*) = 1) as X
ON mr.account = X.account
AND mr.lob = X.lob
JOIN tbl_dropInPkg dip
ON mr.inPkg = dip.inPkgCode
JOIN tbl_dropOutPkg dop
ON mr.outPkg = dop.outPkgCode
JOIN tbl_dropReasons dr
ON mr.reason = dr.reasonCode
LEFT OUTER
JOIN tbl_audit au
ON au.inPkg = mr.inPkg AND au.outPkg = mr.outPkg