Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: Distinct Count on joined tables

    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
    Last edited by Code Monkey 77; 11-09-05 at 15:11.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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

  3. #3
    Join Date
    Nov 2005
    Posts
    4

    Thumbs up

    Brilliant, thanks for that madafaka, works a treat.

    Kind regards,
    James

  4. #4
    Join Date
    Nov 2005
    Posts
    4
    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
    Attached Files Attached Files
    Last edited by Code Monkey 77; 11-12-05 at 09:31.

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212

    Post

    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    4

    Thumbs up

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

    Best regards,
    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •