Cross match in Join

Transact-SQL
1

Hi All,

When I create a join in sql the records are not matching with each other
like this
Capture1

How Can I fix this ?

Thanks ,
Mina

2

Can you give a clue as to what the data is and what you are trying to achieve.
I'm guessing you have some rows in one table with IND and you want to join them to rows in another table with India.
on t1.country = upper(left(t2.country,3))
might do it - but you will get cartesian products of the groups.

3

IND instead of india is not the problem I have function to transform the country to the code
But how can I make IND and india in the same row ?

4

Best solution would be to stream line your tables using a country id pointing to a country table.
Next best solution would be to stream line your tables using same country names.

But I guess you don't have these options since you ask in this forum.

You could do:

select whatever_fields_you_want_to_see
  from yourtable_with_3digit_countryname as a
       left outer join yourtable_with_full_countryname as b
                    on a.yourfield_with_with_3digit_countryname=case b._with_full_countryname
                                                                   when 'India' then 'IND'
                                                                   when 'Great Britain' then 'GB'
                                                                   ...
                                                                   else null
                                                                end

You write what you have a function to convert the country codes. Then you might be able to:

select whatever_fields_you_want_to_see
  from yourtable_with_3digit_countryname as a
       left outer join yourtable_with_full_countryname as b
                    on a.yourfield_with_with_3digit_countryname=yourfunction_to_convert_to_3digit_country(b._with_full_countryname)

It would have been much easier to help, if you provided:

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
  • the query you got so far
  • the country convert function
5

If you don't have a problem joining using IND mapping to India then I don't see how you are getting the result you are.
If IND joins to India then you wouldn't get the result you have (but would have problems with duplicates - the data you have given there would give 4 rows instead of 2).
You presumably have other data that you want to present as what you have shown wouldn't be very useful so you can maybe use that to get rid of duplicates or can number the rows in the query and use that in the join.

6

What are the tables you are joining thatvproduce this undesired result