0

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
3

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.

0

It's not clear what you mean by 35 tables.

You can achieve the result you need from the 3 tables described as following:

SELECT   l.lid
        ,l.name
        ,l.meter
        ,l.address
        ,p.path
        ,p.picinfo

        ,o.owner_name_1
        ,o.owner_family_1
        ,o.owner_mobile_1
        ,o.owner_name_2
        ,o.owner_family_2
        ,o.owner_mobile_2
        ,o.owner_name_3
        ,o.owner_family_3
        ,o.owner_mobile_3
        ,o.owner_name_4
        ,o.owner_family_4
        ,o.owner_mobile_4
        /* etc, up to max number of owners */
FROM land l
  INNER JOIN pic p
    ON l.picid = p.picid
  INNER JOIN (
    SELECT   o1.lid
            ,o1.name AS owner_name_1
            ,o1.family AS owner_family_1
            ,o1.mobile AS owner_mobile_1

            ,o2.name AS owner_name_2
            ,o2.family AS owner_family_2
            ,o2.mobile AS owner_mobile_2

            ,o3.name AS owner_name_3
            ,o3.family AS owner_family_3
            ,o3.mobile AS owner_mobile_3

            ,o4.name AS owner_name_4
            ,o4.family AS owner_family_4
            ,o4.mobile AS owner_mobile_4

            /* etc, up to max number of owners */
    FROM owner o1
      LEFT JOIN owner o2
        ON o1.lid = o2.lid AND o1.ownerid < o2.ownerid
      LEFT JOIN owner o3
        ON o1.lid = o3.lid AND o2.ownerid < o3.ownerid
      LEFT JOIN owner o4
        ON o1.lid = o4.lid AND o3.ownerid < o4.ownerid
      LEFT JOIN owner o5
        ON o1.lid = o5.lid AND o4.ownerid < o5.ownerid

      /* etc, up to max number of owners*/

  ) o
    ON l.lid = o.lid

(In the above example, I only included coded for up to 4 owners; add more LEFT JOIN's as required.

  • do you have any idea how could i join 35 tables and show the related data in just one row for each entity? – Sedighe Rahimi Oct 11 '16 at 14:22
  • the 3 tables that i mentioned above ,were a sample of a little part of it – Sedighe Rahimi Oct 11 '16 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.