Using inner join and left join together

Tables

DECLARE @Land AS table
(
    [lid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [meter] integer NULL,
    [address] integer NULL,
    [picid] integer NULL
);

DECLARE @Owner AS table
(
    [ownerid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [family] varchar(30) NOT NULL,
    [mobile] varchar(30) NULL,
    [lid] integer NOT NULL
);

DECLARE @Pic AS table
(
    [picid] integer PRIMARY KEY,
    [path] varchar(256) NOT NULL,
    [picinfo] varchar(100) NULL
);

Sample data

INSERT @Land 
    ([lid], [name], [meter], [address], [picid])
VALUES
    (1, 'land 1', 1, 1, 1),
    (2, 'land 2', 2, 2, 2);

INSERT @Owner
    ([ownerid], [name], [family], [mobile], [lid])
VALUES
    (1, 'owner 1 of land 1', 'family 1', 'mobile 1', 1),
    (2, 'owner 2 of land 1', 'family 2', 'mobile 2', 1),
    (3, 'owner 3 of land 1', 'family 3', 'mobile 3', 1),
    (4, 'owner 1 of land 2', 'family 4', 'mobile 4', 2),
    (5, 'owner 2 of land 2', 'family 5', 'mobile 5', 2);

INSERT @Pic
    ([picid], [path], [picinfo])
VALUES
    (1, 'Path 1', 'Pic Info 1'),
    (2, 'Path 2', 'Pic Info 2');

Solution

WITH Numbered AS
(
    -- Join the rows and number land owners
    SELECT
        L.[lid],
        L.[name],
        L.[meter],
        L.[address],
        owner_name = O.[name],
        owner_family = O.[family],
        owner_mobile= O.[mobile],
        P.[path],
        P.[picinfo],
        rn = ROW_NUMBER() OVER (
            PARTITION BY L.lid
            ORDER BY O.ownerid)
    FROM @Land AS L
    JOIN @Owner AS O
        ON O.lid = L.lid
    JOIN @Pic AS P
        ON P.picid = L.picid
)
SELECT
    N.[lid],
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.picinfo,
    [owner_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_name ELSE NULL END),
    [owner_family_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_family ELSE NULL END),
    [owner_mobile_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_mobile ELSE NULL END),
    [owner_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_name ELSE NULL END),
    [owner_family_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_family ELSE NULL END),
    [owner_mobile_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_mobile ELSE NULL END),
    [owner_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_name ELSE NULL END),
    [owner_family_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_family ELSE NULL END),
    [owner_mobile_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_mobile ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY 
    N.[lid], -- key column
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.[picinfo]
ORDER BY
    N.lid;

Output

Execution plan

Solution description

The basic idea is that joining the three tables is easy. You don't even need an outer join at all if there is no land without an owner. The problem is that this produces rows, where you want columns in your output, with the columns numbered by owner. The numbering part is easy, using ROW_NUMBER.

Turning rows into columns is known as a pivot, the expanded form of which uses MAX, CASE, and GROUP BY as shown in the above code. You just need to repeat the MAX(CASE... sections to accommodate twelve possible owners.

There are a number of good questions and answers tagged pivot on the site already that you might like to look at. One relevant to your current problem is Need help with SQL Server PIVOT.