• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

T-Sql table joinn

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
0
moni81011
Asked:
moni81011
  • 4
  • 3
1 Solution
 
moni81011Author Commented:
Thank you for your help.  Please provide code to get the full 500.
0
 
Jared_SCommented:
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')
0
 
moni81011Author Commented:
I get an error on
Msg 102, level 15, state1 line 4
Incorrect syntax near '='

select t1.*
case =
when t1.type = 'A' + cast(t2.new as varchar) then
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jared_SCommented:
Is this actual data?
0
 
moni81011Author Commented:
No but it does mimic excatly the real data.
0
 
Jared_SCommented:
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
(or)
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,
t1.[type],
isnull(t2.tier, t1.tier) as tier, 
t1.balance
from
table1 t1 left outer join table2 t2 on t1.ssn = t2.ssn 
and (t1.type = 'A' + t2.new or
t1.type = 'A' + t2.used)
group by t1.ssn, 
t2.score, t1.score,
t1.[type],
t2.tier, t1.tier, 
t1.balance

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?
0
 
moni81011Author Commented:
Overall I ended up using a temp table and doing multiple updates.  Thank you for trying.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now