locked
How to join a table column with another table column which contains comma separated ID's RRS feed

  • Question

  • User2097270768 posted

    Dept table contains DeptId,DeptName.

    Employee table contains EmpId, DeptId

    In employee table contains the data of deptId by comma separated. like 2,5,. In this how to join these two table... I did with using Like operator, can any one tell best solution's for this.

    Monday, December 14, 2015 10:19 AM

Answers

  • User-1716253493 posted

    Try this

    t2.id LIKE '%,' + t1.id + ',%'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2015 10:34 AM

All replies

  • User-1716253493 posted

    Try this

    t2.id LIKE '%,' + t1.id + ',%'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2015 10:34 AM
  • User2097270768 posted

    Thank you, Yes I did the same but any another solutions. 

    and add comma before ','+t2.id like '%,'+t1.id+',%'

    Monday, December 14, 2015 10:47 AM
  • User-986267747 posted

    Hi Rammi44,

    Thank you, Yes I did the same but any another solutions. 

    You could try with the code below.

    	  select * from Dept  F
         JOIN Employee  C
              on CHARINDEX((','+CAST(F.DeptId as varchar(10))+','), (','+CAST(C.DeptId as varchar(10))+',')) > 0

    Besides, there is a similar thread, you could look it.

    http://stackoverflow.com/questions/25082164/join-table-using-columns-which-contains-other-tables-ids-separated-by-commas

    I hope it's helpful to you.

    Best Regards,

    Klein zhang

    Tuesday, December 15, 2015 6:43 AM
  • User77042963 posted

    This table design is bad. Redesign it is a better solution.

    Or to work with this design, you can SPLIT the value with a table-valued function.

    Tuesday, December 15, 2015 2:49 PM