Professional Web Applications Themes

inner join question - Microsoft SQL / MS SQL Server

I want to get the common records of 2 tables. However, it turns out the count of the common records of 2 tables is even larger than the count of table TB1. It is so weird and I have no idea why. Here's the inner join statement I use: SELECT TB1.* FROM TB1 INNER JOIN TB2 ON TB1.RMA = TB2.RMA AND TB1.Parts = TB2.Parts AND TB1.PPID = TB2.PPID; and this SQL statement should get the count of the common records of 2 tables: SELECT COUNT(*) FROM TB1 INNER JOIN TB2 ON TB1.RMA = TB2.RMA AND TB1.Parts = TB2.Parts AND TB1.PPID ...

  1. #1

    Default inner join question

    I want to get the common records of 2 tables. However, it turns out the
    count of the common records of 2 tables is even larger than the count of
    table TB1. It is so weird and I have no idea why. Here's the inner join
    statement I use:

    SELECT TB1.*
    FROM TB1 INNER JOIN
    TB2 ON TB1.RMA = TB2.RMA
    AND TB1.Parts = TB2.Parts
    AND TB1.PPID = TB2.PPID;

    and this SQL statement should get the count of the common records of 2
    tables:

    SELECT COUNT(*)
    FROM TB1 INNER JOIN
    TB2 ON TB1.RMA = TB2.RMA
    AND TB1.Parts = TB2.Parts
    AND TB1.PPID = TB2.PPID;

    I really have no idea what's going on, since I think this SQL statement is
    correct. Please advice.

    Thanks!!!
    John


    John Guest

  2. #2

    Default Re: inner join question

    select count(*)
    from (
    SELECT TB1.*
    FROM TB1 INNER JOIN
    TB2 ON TB1.RMA = TB2.RMA
    AND TB1.Parts = TB2.Parts
    AND TB1.PPID = TB2.PPID
    )derived

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "John Davis" <com> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: inner join question

    The values in the table tb1/tb2 or both of them might be repeating for the
    columns rma,parts,ppid. see following example.
    The row with the values 1,'a',2 is repeated twice in tb1 while is repeated 3
    times in tb2 Therefore if you join them it will give you 6 rows.

    create table tb1( rma int, parts varchar(5), ppid int)
    create table tb2( rma int, parts varchar(5), ppid int)

    insert into tb1 values (1,'a',2)
    insert into tb1 values (1,'a',2)
    insert into tb2 values(1,'a',2)
    insert into tb2 values(1,'a',2)
    insert into tb2 values(1,'a',2)

    SELECT TB1.*
    FROM TB1 INNER JOIN
    TB2 ON TB1.RMA = TB2.RMA
    AND TB1.Parts = TB2.Parts
    AND TB1.PPID = TB2.PPID;

    make sure you have right joins ie you are not missing any additional column
    in the join, if it is correct then there is ambiguity in your table, and you
    will have to correct it.OR you may have to use distinct clause to eliminate
    repeating rows.

    SELECT distinct TB1.*
    FROM TB1 INNER JOIN
    TB2 ON TB1.RMA = TB2.RMA
    AND TB1.Parts = TB2.Parts
    AND TB1.PPID = TB2.PPID;

    --
    -Vishal
    John Davis <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

Similar Threads

  1. INNER JOIN query question
    By Bosconian in forum MySQL
    Replies: 6
    Last Post: February 27th, 11:21 PM
  2. sql join question
    By Scott in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: March 2nd, 06:09 PM
  3. join question - maybe map
    By Gary Stainburn in forum PERL Beginners
    Replies: 1
    Last Post: October 16th, 09:48 AM
  4. SQL Join question
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 24th, 02:08 PM
  5. JOIN/UNION question
    By brian richards in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 02:49 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139