0

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.stack.imgur.com/7aySb.jpg, https://i.stack.imgur.com/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)
Raghav Garg
  • 3,601
  • 2
  • 23
  • 32
Chloe
  • 73
  • 9

1 Answers1

1

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"
Raghav Garg
  • 3,601
  • 2
  • 23
  • 32