You must Sign In to post a response.
  • Category: SQL Server

    Inner join returns multiple records

    I have two table table1 and table2 -tbable1 having 26 records another table2 is having one records for that id, but it returns 26 records instead of 1

    table1
    id
    patentityid
    amount
    26 records for patentityid 111

    table2
    id
    patentityid
    balance
    1 records for patentityid 111

    select * from table1 th
    join table2 ch on th.patentityid=ch.patentityid
    where th.patentityid=111 and ch.balance >1
  • #765362
    Hi sankar,
    use following query:
    select distinct ch.* from table1 th join table2 ch on th.patentityid=ch.patentityid where th.patentityid=111 and ch.balance >1
    Hope it helps.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com

  • #765368
    Hi Sankar,

    Ofcourse it's return multiple records because in your select statement you are calling all the columns (*) available in table1 and table2. In table1 records and table2 records might be different, in that case it will return all the rows, in this case if you want to clear the issue you have to choose the required columns alone rather than selecting all the columns, still you are not getting the result as you want then use DISTINCT keyword for selected columns, it will return the distinct result.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments