Query stuff result left join on multiple ids

Asked Aug 12 '21 at 09:34

Active Aug 12 '21 at 09:55

Viewed 39 times

This is my SQL code

SELECT
    fu.Id,
    fu.DepartmentId,
    t.Name AS DepartmentName
FROM
    FactoryUser fu
LEFT JOIN
    Department t ON CAST(t.Id AS nvarchar(50)) IN (fu.DepartmentId)
WHERE
    fu.Id = 5619573199567804058

Result

Id      DepartmentId            DepartmentName
561957  519985,468063,502604    NULL

departmentId Associate department table I hope query department name of all ids like "A-deptmentname,B-deptmentname,c-deptmentname"

Id      DepartmentId            DepartmentName
561957  519985,468063,502604    A-deptname,B-deptname,c-deptname

edited Aug 12 '21 at 09:55

Dale K

25,246
15
42
71

asked Aug 12 '21 at 09:34

beiduoan

What is your version of SQL Server? – Tim Biegeleisen Aug 12 '21 at 09:35
6

Looks like the problem is your design; you're storing delimited data in your database. Fix the design, fix the problem. – Thom A Aug 12 '21 at 09:36
@TimBiegeleisen sql server 2012 – beiduoan Aug 12 '21 at 09:51
@Larnu The old system did not do one-to-many design, but I need to solve the problem now – beiduoan Aug 12 '21 at 09:52
*"but I need to solve the problem now"* Then start fixing the design *now*. As an interim solution, however, I would suggest looking into (set based) string splitters but that isn't the solution, just a work around. As I said, fix the design, fix the problem. – Thom A Aug 12 '21 at 09:56
@Larnu I can't modify the data structure, I can only use sql to achieve this – beiduoan Aug 12 '21 at 10:03

Query stuff result left join on multiple ids

0 Answers0