I need to build my query using SQL Server Management Studio Query Designer
I need to display a concatenated string of tbl_B.Name + tbl_C.Name + tbl_D.Name
, if Id
in tbl_E/tbl_User
is equal to UserId
in tbl_A
This is the Table structure https://i.sstatic.net/7aySb.jpg, https://i.sstatic.net/5t550.jpg
tbl_A UserId = Id in tbl_E/tbl_User
tbl_B_Id = Id in tbl_B
tbl_C_Id = Id in tbl_C
tbl_D_Id = Id in tbl_D
I tried the below code but doesn't display Name field from tbl_B, tbl_C and tbl_D
SELECT tbl_B.Name AS Expr1, tbl_C.Name AS Expr2, tbl_D.Name AS Expr3, tbl_A.UserId, [tbl_E].Id AS Expr5
FROM tbl_B
INNER JOIN tbl_D ON tbl_B.Id = tbl_D.Id
INNER JOIN tbl_A ON tbl_B.Id = tbl_A.Id
INNER JOIN tbl_C ON tbl_B.tbl_CId = tbl_C.Id AND tbl_D.Id = tbl_C.tbl_DId
INNER JOIN [tbl_E] ON tbl_B.Id = [tbl_E].Id AND tbl_A.UserId = [tbl_E].Id
WHERE (tbl_A.UserId = [tbl_E].Id)
Below query will give you the concatenated string of names for every userId, which has entry in all the tables.
SELECT
"tbl_A"."UserId",
CONCAT("tbl_B"."Name", '-', "tbl_C"."Name", '-', "tbl_D"."Name"),
"tbl_B"."Name" AS Expr1,
"tbl_C"."Name" AS Expr2,
"tbl_D"."Name" AS Expr3
FROM "tbl_E"
INNER JOIN "tbl_A" ON "tbl_A"."UserId" = "tbl_E"."id"
INNER JOIN "tbl_B" ON "tbl_B"."id" = "tbl_A"."tbl_B_id"
INNER JOIN "tbl_C" ON "tbl_C"."id" = "tbl_B"."tbl_C_id"
INNER JOIN "tbl_D" ON "tbl_D"."id" = "tbl_C"."tbl_D_id"