Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Join Returns too many rows

    Hi

    I'm sure this is a real noob question and it may be something I have know the answer to in the past but I can't remember and its been driving me mad for hours. If anyone can tell me how to do this it would make my day!

    I have simplified the problem for the purpose of clarity and have attached a sript to create a simple example table.

    the table looks like this:
    Code:
    id         cMatch        cData
    1	A	A1
    2	B	B1
    3	C	C1
    4	B	B2
    5	A	A2
    6	B	B3
    I want to be able to do a join on the two table that only returns the following:
    Code:
               t1.cData     t2.cData
    	A1	A2
    	B1	B2
    	B1	B3
    The Closest I can get is with the following qry:
    Code:
    SELECT t1.cdata, t2.cdata from tmp_Table1 t1
    JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch AND t1.cdata<>t2.cdata
    WHERE t1.cdata<t2.cdata
    ORDER BY t1.cdata, t2.cdata
    Which returns:
    Code:
               t1.cData     t2.cData
    	A1	A2
    	B1	B2
    	B1	B3
    	B2	B3
    Last edited by AndyM; 07-18-03 at 18:18.

  2. #2
    Join Date
    Jul 2003
    Posts
    2
    Not sure if I attached the script last time so I thought I'd make sure.

    thanks in advance for all your help!

    Andy
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Hi

    while not knowing your specific database, this will work with the example you provided:

    SELECT MIN(cdata1), cdata2 FROM
    (
    SELECT t1.cdata AS cdata1, t2.cdata AS cdata2 FROM tmp_Table1 t1
    INNER JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch
    AND t1.cData <> t2.cData
    AND t1.id < t2.id)
    AS subtable
    GROUP BY cdata2

    you may have to change the aggragation function that evaluates the correct value to choose.

Posting Permissions

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