Terrence Senthuran Terrence Senthuran - 2 years ago 57
SQL Question

Get values from 2 tables based on ID

Consider below tables

Job Table

JobID AnswerID UserID
1 1,2 1
2 2,3 2
3 1,3 3

Answer Table

AnswerID Answer QuestionID
1 Clean 1
2 Install 1
3 Other 2

For this I need to get the result as below

JobID Answer UserID
1 Clean,Install 1
2 Install,Other 2
3 Clean,Other 3

Please help to write MSSQL query for this.

Answer Source

This is too long for a comment.

You are storing a list of ids as a comma separated list. This is a really bad idea for several reasons:

  • Storing numbers as strings is a bad idea.
  • You cannot define foreign key relationships.
  • SQL does not have great support for strings.
  • Any attempt to join to the original table will be inefficient, because of the type conversion.
  • Such a structure violates the idea that a column contains a single value.

There is a proper way to store lists in a relational database. It is called a "table". You want a junction table with one row per job and answer. I would call it JobAnswers.

With the proper data structure, your query would be trivial.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download