"Conversion failed when converting the varchar value to data type int

• tmc

  SSC Enthusiast

  Points: 172

  More actions

  November 9, 2019 at 9:06 pm

  #3696504

  Hello to everyone

  I am started my new carrer as data analyst using SQL.

  am new entry and i have to join the following charts

  I

  i used this code

  Select

  Negozi.descrizione,

  StrutturaOrganizzativa.descrizione

  from Negozi

  inner join StrutturaOrganizzativa

  on StrutturaOrganizzativa.descrizione=Negozi.fk_responsabile

  But unfortunately SQL give me back this result

  Msg 245, Level 16, State 1, Line 1

  Conversion failed when converting the varchar value 'Mario Rossi' to data type int.

  Please  anyone can tell me where i wrong trying to solve it ?

  Thank a lot !

   

   

• Phil Parkin

  SSC Guru

  Points: 246950

  More actions

  November 9, 2019 at 9:29 pm

  #3696505

  Sure. This join

  on StrutturaOrganizzativa.descrizione=Negozi.fk_responsabile

  is the problem, because you're trying to match a string and an integer. Columns which you JOIN on should ideally have the same datatype.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Sumathi

  Valued Member

  Points: 61

  More actions

  November 11, 2019 at 9:55 pm

  #3696881

  StrutturaOrganizzativa.descrizione is string type and Negozi.fk_responsabile is integer type. When you join on two columns both should be of same datatype. Use this:

  Select

  Negozi.descrizione,

  StrutturaOrganizzativa.descrizione

  from Negozi

  inner join StrutturaOrganizzativa

  on StrutturaOrganizzativa.pk_id = Negozi.pk_id

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  November 11, 2019 at 10:51 pm

  #3696893

  I'm guessing the value in Negozi.fk_responsabile points to the primary key in StrutturaOrganizzativa (StrutturaOrganizzativa.pk_id)

  'pk' in relational databases often stands for 'Primary Key', and 'fk' often means 'Foreign Key'.

  A Primary Key is a unique value that identifies a row in a table. The 'pk_id' column is probably the Primary Key in the StrutturaOrganizzativa table, and can be used to uniquely identify any row in the table. The 'fk_responsabile' column in StrutturaOrganizzativa is likely a Foreign Key, meaning 'use this value to find the row in another place'. So we'll JOIN the two tables by connecting the foreign key to the remote primary key.

  Note the change to the JOIN clause:

  Select Negozi.descrizione, StrutturaOrganizzativa.descrizione
    from Negoziinner 
    join StrutturaOrganizzativa on StrutturaOrganizzativa.pk_id=Negozi.fk_responsabile

  -Eddie

  Eddie Wuerch
  MCM: SQL