Results 1 to 2 of 2

Thread: Testing links

  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Testing links

    Hello,

    I have a problem with my database.

    I have records ( key =ID) which are gathered in different fields GROUP ( from “1ECC” to “8ECC” in this example).

    Example: The IDs “14238” and “838b” are gathered in the GROUP “2ECC”

    I would like to identify GROUPs where at least one ID has a link different from the links of the other IDs in the same GROUP.

    Example:

    The GROUP “1ECC” is made of 2 IDs which have the same LINK=”99”. The field RESULT of each ID is “A”.

    The GROUP “5ECC” is made of 2 IDs which have different LINKs=”11” and “12”. The field RESULT of each ID is “B”.

    The GROUP “6ECC” is made of 3 IDs of which one has a different LINK ( “0”) to the 2 others (“66”). The field RESULT of each ID is “B”.

    This example is actually illustrating a bigger base, I therefore need to write something which repeats tests. Unfortunately I am too beginner and I do not know how to do it 

    Database: http://cjoint.com/?gcaSX28bUp

    Result expected: http://cjoint.com/?gcaTL5cGFe


    Can someone help me?

    Thanks thousand times in advance
    Attached Thumbnails Attached Thumbnails jpg.jpg  
    Attached Files Attached Files

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    piflechien73, This might work (if I followed what you want).
    Code:
    SELECT *
    FROM table-name
    WHERE GROUP NOT IN(SELECT GROUP_TAB.GROUP
                      FROM (SELECT GROUP, COUNT(*) AS CNT
                            FROM table-name
                            GROUP BY GROUP
                           ) AS GROUP_TAB
                         , (SELECT GROUP, LINK, COUNT(*) AS CNT
                            FROM table-name
                            GROUP BY GROUP, LINK
                           ) AS LINK_TAB
                      WHERE GROUP_TAB.CNT = LINK_TAB.CNT
                     )
    What this should do is count the number of distinct Group values:
    Code:
    SELECT GROUP, COUNT(*) AS CNT
    FROM table-name
    GROUP BY GROUP
    Result:
    Code:
    GROUP	CNT
    1ECC	2
    2ECC	2
    3ECC	2
    4ECC	2
    5ECC	2
    6ECC	3
    7ECC	2
    8ECC	2
    Count the number of Distinct Group/Link combination values:
    Code:
    SELECT GROUP, LINK, COUNT(*) AS CNT
    FROM table-name
    GROUP BY GROUP, LINK
    Result:
    Code:
    GROUP	LINK	CNT
    1ECC	99	2
    2ECC	44	2
    3ECC	77	2
    4ECC	55	2
    5ECC	11	1
    5ECC	12	1
    6ECC	 0	1
    6ECC	66	2
    7ECC	777	2
    8ECC	888	2
    These results are joined by the CNT value and returns a list of GROUP values where the number of Distinct Group values and Distinct Group/Link values are the same (all the LINKS are identical).
    Code:
    (SELECT GROUP_TAB.GROUP
     FROM (SELECT GROUP, COUNT(*) AS CNT
           FROM table-name
           GROUP BY GROUP
          ) AS GROUP_TAB
        , (SELECT GROUP, LINK, COUNT(*) AS CNT
           FROM table-name
           GROUP BY GROUP, LINK
          ) AS LINK_TAB
     WHERE GROUP_TAB.CNT = LINK_TAB.CNT
    )
    Results:
    Code:
    GROUP
    1ECC
    2ECC
    3ECC
    4ECC
    7ECC
    8ECC
    Finally, these results are in the NOT IN subquery so all of the GROUP values that do NOT match the list of GROUP values that match produces the results:
    Code:
    GROUP	LINK
    5ECC	11
    5ECC	12
    6ECC	 0
    6ECC	66
    6ECC	66
    This is the first thing that came to mind. There may be other ways to get the same results.

Posting Permissions

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