Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    62

    Unanswered: Inner join query problem

    I have two tables

    table xyz
    primary key
    colA
    colB
    colC
    colD

    table abc
    primary key
    col1
    col2
    col3
    col4
    col5
    col6

    I am joining these two tables as follows.

    select distinct x.col3, <some columns from xyz>
    where x.colA = 123456
    and x.colA = a.col1
    and x.colB = a.col2
    and x.colC = a.col5
    and x.colD = a.col6

    I want all the rows from xyz matching to a row in abc. The problem is that this query is returning all rows from xyz
    plus some extra rows from abc which I do not want to see. I am joining the two tables using the common four columns.
    There is no matching column in xyz for col3 and col4 of abc.

    See the counts from the tables.

    select count(*) from abc
    where col1 = 123456;

    COUNT(*)
    -------
    14128

    select count(*) from xyz
    where colA = 123456;

    COUNT(*)
    -------
    14070

    I was expecting the join query to return only 14070 rows. However it returns 14116 rows (46 extra rows).

    Is there anyway to get around this problem using just one query ? Any help is appreciated.

    I am using Oracle 9.2.0.6.0.

    Thanx..
    -Bheem

  2. #2
    Join Date
    May 2002
    Posts
    62
    Sorry, the actual query is like this.

    select distinct a.col3, <some columns from xyz>
    from xyz x, abc a
    where x.colA = 123456
    and x.colA = a.col1
    and x.colB = a.col2
    and x.colC = a.col5
    and x.colD = a.col6

    Thanks
    -Bheem

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I don't see why you need a distinct statement.
    how many do you get just running this?
    PHP Code:
    select count(*)
    from xyz xabc a
    where x
    .colA 123456
    and x.colA a.col1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2002
    Posts
    62
    Please ignore this post, as it was a data problem and I had to join another table to get the correct result set.

    Thanks
    -Bheem

Posting Permissions

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