Question on joins with multi tables

Question

I have 4 tables ( 1 to many table relation) as shown Main , Worker , Location and MiddleName Table

Any suggestions on how to get the result. I tried joins and it never worked.

Answer 1

CREATE TABLE #main
(
    ID INT
  , firstName VARCHAR(12)
);

CREATE TABLE #worker
(
    ID INT
  , WorkerID INT
);

CREATE TABLE #location
(
    ID INT
  , Location VARCHAR(12)
);

CREATE TABLE #middlename
(
    ID INT
  , MiddleName VARCHAR(12)
);

INSERT INTO #main
VALUES
(1, 'Jim')
, (2, 'John');

INSERT INTO #worker
VALUES
(1, 123)
, (1, 2345)
, (2, 4534);

INSERT INTO #location
VALUES
(1, 'Asia')
, (1, 'America')
, (1, 'Africa');

INSERT INTO #middlename
VALUES
(2, 'Jack');

If you are just trying to join the tables based on what you said in the comments, do this -

SELECT m.ID
     , m.firstName
     , w.WorkerID
     , l.Location
     , mn.MiddleName
FROM #main AS m
    LEFT JOIN #worker AS w
        ON w.ID = m.ID
    LEFT JOIN #location AS l
        ON l.ID = m.ID
    LEFT JOIN #middlename AS mn
        ON mn.ID = m.ID
WHERE m.ID = 1;

ID	firstName	WorkerID	Location	MiddleName
1	Jim	123	Asia	null
1	Jim	123	America	null
1	Jim	123	Africa	null
1	Jim	2345	Asia	null
1	Jim	2345	America	null
1	Jim	2345	Africa	null

But if you are trying to get that weird result, it's junk, but do this -

SELECT base.ID
     , base.firstName
     , IIF(base.ID = w.ID, w.WorkerID, NULL) AS WorkerID
     , base.Location
     , base.MiddleName
FROM
(
    SELECT m.ID
         , m.firstName
         , l.Location
         , mn.MiddleName
         , ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY (SELECT NULL)) AS rn
    FROM #main AS m
        LEFT JOIN #location AS l
            ON l.ID = m.ID
        LEFT JOIN #middlename AS mn
            ON mn.ID = m.ID
    WHERE m.ID = 1
) AS base
    INNER JOIN
    (
        SELECT ID
             , WorkerID
             , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        FROM #worker
    ) AS w
        ON base.rn = w.rn;

ID	firstName	WorkerID	Location	MiddleName
1	Jim	123	Asia	null
1	Jim	2345	America	null
1	Jim	null	Africa	null

demo DB Fiddle