SQL Server – Using Inner Join and Left Join Together

join;pivotsql server

I have about 35 tables. I want to use them in the same query. I do not know how it possible.

I have three tables:

land

lid
name
meter
address
picid

owner

ownerid
name
family
mobile
lid

Every land can have a maximum of 12 owners.

pic

picid
path
picinfo

The result that I need:

lid, name, meter, address, path, picinfo, owner_name_1, owner_family_1,  
owner_mobile_1, owner_name_2, owner_family_2, owner_mobile_2, ....  
owner_name_12, owner_family_12, owner_mobile_12

Best Answer

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

Result

Execution plan

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 on the site already that you might like to look at. One relevant to your current problem is Need help with SQL Server PIVOT.

Related Question