T-Sql table joinn

Posted on 2012-09-17
Last Modified: 2012-09-26
Table 1:
ssn   score  type  tier  balance
123    100    A1     A      100
123    105    A2     B      120
123    110    A3     A      100
123              A1     C      140
456    100    A3     A      102
756              A32   D      105

Table 2:
id     date     score    ssn       tier    new    used
998  1/07     107       123       B       1          71
996  1/11     105       123       A       42         2        
997  1/10     100       123       D       3           1

I need:
- all the record of table 1, regardless if there is a matching record in table2 or not
- the link between the tables is table1.ssn = table2.ssn and table1.type = 'A' + new or used (depending where the match is in new or used)
- If table 2 has a matching record used table2.score and table2.tier over table1 values
- I don't need duplicates
- results should look like this:

ssn   score  type  tier  balance
123    107    A1     B      100
123    105    A2     A      120
123    100    A3     D      100
123    107    A1     B      140
456    100    A3     A      102
756              A32   D      105
Question by:moni81011

    Author Comment

    Thank you for your help.  Please provide code to get the full 500.
    LVL 12

    Expert Comment

    created the data set and am re-writing... here is a test data set for other EE users that want to test their query results

    declare @t1 table (ssn varchar(25), score varchar(25), [type] varchar(25), tier varchar(25), balance varchar(25))
    declare @t2 table (id varchar(25), Dt varchar(25), score varchar(5), ssn varchar(25), [tier] varchar(25), new varchar(25), used varchar(25))

    insert into @t1 values
    ('123', '100', 'A1', 'A', '100')
    insert into @t1 values
    ('123','105' , 'A2' , 'B', '120')
    insert into @t1 values
    ('123',  '110', 'A3', 'A', '100')
    insert into @t1 values
    ('123', null, 'A1', 'C','140')

    insert into @t2 values
    ('998', '1/07', '107', '123','B','1','71')
    insert into @t2 values
    ('996', '1/11', '105','123','A', '42','2')        
    insert into @t2 values
    ('997', '1/10', '100','123','D', '3','1')

    Author Comment

    I get an error on
    Msg 102, level 15, state1 line 4
    Incorrect syntax near '='

    select t1.*
    case =
    when t1.type = 'A' + cast( as varchar) then
    LVL 12

    Expert Comment

    Is this actual data?

    Author Comment

    No but it does mimic excatly the real data.
    LVL 12

    Accepted Solution

    If I only use the join criteria that was requested more matches will be created in the output than are shown in your example.

    This is written with the join criteria:

    1) table 1 ssn = table 2 ssn
    2) table 1 type = table 2 'A' + New
    3) table 1 type = table 2 'A' + Used

    and then displays the score and tier based on whether or not a match is found in table 2.

    select t1.ssn, 
    isnull(t2.score, t1.score) as score,
    isnull(t2.tier, t1.tier) as tier, 
    table1 t1 left outer join table2 t2 on t1.ssn = t2.ssn 
    and (t1.type = 'A' + or
    t1.type = 'A' + t2.used)
    group by t1.ssn, 
    t2.score, t1.score,
    t2.tier, t1.tier, 

    Open in new window

    The results of the join criteria against the test data look like this (only showing ssn 123):

    ssn      score      type      tier      balance
    123      100      A1      D      140
    123      100      A1      D      100
    123      100      A3      D      100
    123      105      A2      A      120
    123      107      A1      B      140
    123      107      A1      B      100

    You were pretty clear in your join criteria. Do you need it modified to try and achieve that output, or does the criteria take priority over the output listed in the example?

    Author Closing Comment

    Overall I ended up using a temp table and doing multiple updates.  Thank you for trying.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now