sqlsql-serverquery-builderquery-designer

How to display data from 4 different tables - Query Designer


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)

Solution

  • 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"