Post reply

Combine Two Tables

  • July 8, 2010 at 9:39 am

    #224683

    Hi,

    I'm trying to select distinct records from TableA and TableB below, but I'm getting multiple records. For example ClientID 102 returns 24 record using the query below. What is the best way to avoid duplicated records.

    select *

    from TableA a inner join TableB on (a.clientid = b.clientid)

    order by a.ClientID

    TableA

    TAiD ClientIDLicenseID InUse ProductVersion

    5331021 85004.5.2

    5341022 6004.5.8

    5351023 4614.5.2

    5361024 4504.5.6

    5271031 46495.2.2

    5281032 15.2.2

    5291033 1325.2.2

    5301036 05.2.10

    5311034 65.2.2

    5321038 44865.2.10

    TableB

    TBiDClientID ProductNMProdDesc ProdID ProdType

    12102ABC-DC1ABC DC 192-162DC

    13102ABC-DC2ABC DB2 192-163DC

    14102ABC-TT ABC Tele 192-166TT

    15102ABC-WE1ABC We1 192-164WE

    16102ABC-WE2ABC We2 192-165WE

    17102ABCTestABC Test 192-167TEST

    18103AVA-NOSAva NOS 192-513NOS

    19103AVA-TEST Ava Test 192-561TEST

    20103AVA-WEBAva Web 192-560WEB

    Thanks,

  • July 8, 2010 at 9:45 am

    #1190337

    Distinct based upon what?

    Your query is producing the correct results for the data you have.

    You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 8, 2010 at 9:50 am

    #1190339

    You have 4 records for ClientID 102 in TableA, and 6 records for 102 in TableB. An inner join will match up each record in TableA with a corresponding record in TableB -- and 6 * 4 = 24 records. Each of which is distinct in the sense that, taken as a whole, the joined record is different from any other joined record.

    Can you show what you expect the results of your query to look like? What parts of the records are you considering when you say you want "distinct" records?

    Rob Schripsema
    Propack, Inc.

  • July 8, 2010 at 11:52 am

    #1190411

    select a.*, b.*

    from TableA a

    inner join (

    select clientid, MAX(tbid) AS tbid

    from tableB

    group by clientid

    ) AS bMax on bMax.clientid = a.clientid

    inner join TableB b on (bMax.clientid = b.clientid and bMax.tbid = b.tbid)

    order by a.ClientID

    Scott Pletcher, SQL Server MVP 2008-2010

  • July 9, 2010 at 9:11 am

    #1190859

    Thanks guys, I don't think it will work the way I wanted it to work for the exact reason stated. Please disregard.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply