Distinct Count on joined tables

Code Monkey 77 · #1 (**permalink**) 11-09-05, 14:07

Hi,

I have two tables Contact1 and Contsupp. Both have a field Accountno which will be in Table A once and Table B at least once (if at all). I'm trying to perform a query which returns all the matches of Accountno (subject to a where clause) BUT to return matches once. ie distinctly. This works fine when I return only the Accountno column, however when I return more columnns, duplication occurs.

SELECT DISTINCT
CONTACT1.ACCOUNTNO, CONTACT1.COMPANY, CONTSUPP.ACCOUNTNO AS Expr1, CONTSUPP.RECID, CONTSUPP.LINKACCT, CONTSUPP.COUNTRY, CONTSUPP.ZIP, CONTACT1.KEY3, CONTACT1.CONTACT, CONTACT1.CREATEON

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTACT1.ACCOUNTNO <> 'PTG')

ORDER BY CONTACT1.ACCOUNTNO

So this gives me dups.

Whereas this:

SELECT DISTINCT
CONTACT1.ACCOUNTNO

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')

GROUP BY
CONTACT1.ACCOUNTNO

I believe works. Any ideas??

Thanks,
James

madafaka · #2 (**permalink**) 11-09-05, 16:26

Problem is values in additional columns

Code:

CONTACT1.COMPANY, CONTSUPP.ACCOUNTNO AS Expr1, CONTSUPP.RECID, CONTSUPP.LINKACCT, CONTSUPP.COUNTRY, CONTSUPP.ZIP, CONTACT1.KEY3, CONTACT1.CONTACT, CONTACT1.CREATEON

are different for the same ACCOUNTNO

example: you have

Code:

CONTACT1.ACCOUNTNO  CONTACT1.KEY3
123                             'Z'
123                             'C'

so when you select distinct just ACCOUNTNO result IS
123
but select distinct CONTACT1.ACCOUNTNO CONTACT1.KEY3
result is
123 Z
123 C

Solution: you have to decide which KEY3 you select if you wanna have unique ACCOUNTNO. For this use group by:

Code:

select CONTACT1.ACCOUNTNO, max(CONTACT1.KEY3)
from ...
group by
CONTACT1.ACCOUNTNO 

result

123 Z

so your code could be:

Code:

SELECT 
CONTACT1.ACCOUNTNO, max(CONTACT1.COMPANY), max(CONTSUPP.ACCOUNTNO) AS Expr1, max(CONTSUPP.RECID), max(CONTSUPP.LINKACCT), max(CONTSUPP.COUNTRY), max(CONTSUPP.ZIP), max(CONTACT1.KEY3), max(CONTACT1.CONTACT), max(CONTACT1.CREATEON)

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTACT1.ACCOUNTNO <> 'PTG')

GROUP BY CONTACT1.ACCOUNTNO

ORDER BY CONTACT1.ACCOUNTNO

Code Monkey 77 · #3 (**permalink**) 11-10-05, 04:31

Brilliant, thanks for that madafaka, works a treat.

Kind regards,
James

Code Monkey 77 · #4 (**permalink**) 11-12-05, 08:22

madafaka, on closer examination, the query does supress the duplication of accountno however, it still returns the ACCOUNTNO from CONTSUPP even if the 'PTG' exists in CONTSUPREF for the same ACCOUNTNO.

I've trimmed my query down to:

SELECT
CONTACT1.ACCOUNTNO, MAX(CONTACT1.COMPANY) AS Expr1, MAX(CONTSUPP.ACCOUNTNO) AS Expr2, MAX(CONTACT1.CONTACT) AS Expr3, MAX(CONTSUPP.CONTSUPREF) AS Expr4

FROM
CONTACT1 LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO

WHERE
(CONTACT1.KEY3 LIKE 'APO%') AND (CONTSUPP.CONTSUPREF <> 'PTG')

GROUP BY CONTACT1.ACCOUNTNO

ORDER BY CONTACT1.ACCOUNTNO

CONTACT1 will only ever contain ACCOUNTNO once, CONTSUPP on the other hand may have multiple instances of the same ACCOUNTNO. If CONTSUPREF contains 'PTG' in CONTSUPP for a particular ACCOUNTNO, I want the query to suppress the ACCOUNTNO altogether, but ONLY if 'PTG' is in CONTSUPP for that particular ACCOUNTNO.

In the attached example, I would want examples 2 and 3 to show but not example 1.

I hope that makes sense.

Thanks,
James

madafaka · #5 (**permalink**) 11-13-05, 13:50

Code Monkey 77,
to be honest I'm not 100% clear with your requirements.

If there's an ACCOUNTNO in CONTSUPP where CONTSUPREF = 'PTG'

1) you don't want to select ACCOUNTNO at all (even from CONTACT1)?

2) you want to select ACCOUNTNO from CONTACT1 but do not join it with record in CONTSUPP where CONTSUPREF = 'PTG'. basically skip records in CONTSUPP where CONTSUPREF = 'PTG'?

For the first case there are more options how to realise it:

Code:

SELECT
     CONTACT1.ACCOUNTNO, 
     MAX(CONTACT1.COMPANY) AS Expr1, 
     MAX(CONTSUPP.ACCOUNTNO) AS Expr2, 
     MAX(CONTACT1.CONTACT) AS Expr3, 
     MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
     CONTACT1 
     LEFT OUTER JOIN CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE (CONTACT1.KEY3 LIKE 'APO%')
AND  CONTACT1.ACCOUNTNO NOT IN (SELECT ACCOUNTNO 
                                                    FROM CONTSUPP 
                                                    WHERE CONTSUPREF = 'PTG'
                                                   )
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO

for the second case try:

Code:

SELECT
     CONTACT1.ACCOUNTNO, 
     MAX(CONTACT1.COMPANY) AS Expr1, 
     MAX(CONTSUPP.ACCOUNTNO) AS Expr2, 
     MAX(CONTACT1.CONTACT) AS Expr3, 
     MAX(CONTSUPP.CONTSUPREF) AS Expr4
FROM
     CONTACT1 
     LEFT OUTER JOIN (SELECT ACCOUNTNO, CONTSUPREF FROM CONTSUPP WHERE CONTSUPREF <> 'PTG') CONTSUPP ON CONTACT1.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE (CONTACT1.KEY3 LIKE 'APO%')
GROUP BY CONTACT1.ACCOUNTNO
ORDER BY CONTACT1.ACCOUNTNO

I don't know what DB do you use, so hopefully there won't be an issue with syntax.

Code Monkey 77 · #6 (**permalink**) 11-16-05, 07:40

Thanks for your help Madafka, the first statement is the one that works. Apologies if my request was a little unclear.

Best regards,
James

Internet Services

Web Development

Digital Marketing

Consumer Tech