Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Post Unanswered: Looping query results

    please help with this query. It should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..

    SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
    FROM VENDFIL A, VENDFIL B, VENDFIL C
    GROUP BY A.FEDTID, B.VENDOR

    Thanks..

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have a full-on, three-way Cartesian Join. If the code that you published is complete/correct and it produced between 4,000,00 and 5,000,000 result rows, then you have somewhere between 158-170 vendors in the VENDFIL table. If you had at most 170 rows and you expected more than 200 rows, then you have omitted one or more critical pieces of information about your problem.

    Without more information, there is no way that I can help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Holy cartesian join, Batman!

    You need to write JOINs. At the moment, you've not told the query which columns in one table relate to the next, so it just assumes you want every single combination of values available.

    For instance:
    TableA has rows: A1, A2 & A3
    TableB has rows: B1, B2, B3, B4 & B5

    A cartesian join will yield the following resultset:

    A1 B1
    A1 B2
    A1 B3
    A1 B4
    A1 B5
    A2 B1
    A2 B2
    A2 B3
    A2 B4
    A2 B5
    A3 B1
    A3 B2
    A3 B3
    A3 B4
    A3 B5

    See the problem?

    ---


    Why are you joining the same table to itself three times? What are you actually trying to achieve? Your query as it is posted won't actually parse, so methinks you're masking the problem with the code you've posted.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2015
    Posts
    27
    you are correct. It was Cartesian.
    I was able to use the HAVING clause to resolve.

    Thanks for the feedback...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's the wrong resolution.
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    khan, it should be something along the lines of:

    Code:
    select some_columns
       from table1 1st_iteration
    inner join table1 2nd_iteration
         on 1st_iteration.key_column = 2nd_iteration.key_column
       and 2nd_iteration.column = whatever criteria you need for the reason you are joining to the same table a second time
    inner join table1 3rd_iteration
         on 1st_iteration.key_column = 3rd_iteration.key_column
       and 3rd_iteration.column = whatever criteria you need for the reason you are joining to the same table a third time
    where some_criteria = some_columns
    Dave

Tags for this Thread

Posting Permissions

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