Forbinn Forbinn - 3 months ago 10
SQL Question

SQL: JOIN multiple tables with different size

I am not someone who works a lot with database so the answer can be easy but I am unable to find it.
Here is a simplified version of my database (which is in SQLITE btw):
database image

I have the following values in my database (I only put the id here because only them are relevant):


  • device.id: 1, 2, 3

  • capability.id: 10, 20, 30, 40, 50

  • action.id: 100, 200, 300, 400, 500

  • action.id_capability: 10, 30, 40, 10, 50



And here are entries in map_device_cap:


  • 1, 10

  • 1, 30

  • 1, 40

  • 2, 10

  • 2, 50

  • 3, 20



Entries in map_device_action:


  • 1, 100

  • 1, 200

  • 1, 300

  • 2, 400

  • 2, 500



Now I want to create SELECT request that group tables map_device_cap and map_device_action. In this case the result will be:


  • 1, 10, 100

  • 1, 30, 200

  • 1, 40, 300

  • 2, 10, 400

  • 2, 50, 500

  • 3, 20, null



NOTE: the table map_device_cap will ALWAYS be larger than map_device_action.

I have first try the following request:

SELECT map_device_cap.id_device, map_device_cap.id_capability,
map_device_action.id_action
FROM map_device_cap NATURAL LEFT JOIN map_device_action


But this request gave me the following result:


  • 1, 10, 100

  • 1, 10, 200

  • 1, 10, 300

  • 1, 30, 100

  • 1, 30, 200

  • 1, 30, 300

  • 1, 40, 100

  • 1, 40, 200

  • 1, 40, 300

  • 2, 10, 400

  • 2, 10, 500

  • 2, 50, 400

  • 2, 50, 500

  • 3, 20, null



As you can see there are duplicates entries and I don't know how to remove them.

If anyone has an idea or need more information, please let me know.

Regards

Answer

one way to do it is like this

SELECT c.id_device,
       c.id_capability,
       a.id
FROM map_device_cap c
     JOIN action a ON c.id_capability = a.id_capability
     JOIN map_device_action ma ON a.id = ma.id_action
                                   AND c.id_device = ma.id_device
UNION
SELECT c.id_device,
       c.id_capability,
       NULL
FROM map_device_cap c
     LEFT JOIN map_device_action ma ON c.id_device = ma.id_device
WHERE ma.id_device IS NULL;

Complete Script which I used for my testing:

IF OBJECT_ID('tempdb..#map_device_cap') IS NOT NULL
    DROP TABLE #map_device_cap;

CREATE TABLE #map_device_cap
(id_device INT,
 id_capability       INT
);

INSERT INTO #map_device_cap
VALUES
(1,
 10
);

INSERT INTO #map_device_cap
VALUES
(1,
 30
);

INSERT INTO #map_device_cap
VALUES
(1,
 40
);
INSERT INTO #map_device_cap
VALUES
(2,
 10
);
INSERT INTO #map_device_cap
VALUES
(2,
 50
);
INSERT INTO #map_device_cap
VALUES
(3,
 20
);

select * from #map_device_cap

IF OBJECT_ID('tempdb..#map_device_action') IS NOT NULL
    DROP TABLE #map_device_action;

CREATE TABLE #map_device_action(id_device INT,
 id_action       INT);

INSERT INTO #map_device_action
VALUES
(1,
 100
);

INSERT INTO #map_device_action
VALUES
(1,
 200
);

INSERT INTO #map_device_action
VALUES
(1,
 300
);

INSERT INTO #map_device_action
VALUES
(2,
 400
);

INSERT INTO #map_device_action
VALUES
(2,
 500
);


select * from #map_device_action


IF OBJECT_ID('tempdb..#action') IS NOT NULL
    DROP TABLE #action;

CREATE TABLE #action(id INT,
 id_capability       INT);

INSERT INTO #action
VALUES
(100,
 10
);

INSERT INTO #action
VALUES
(200,
 30
);

INSERT INTO #action
VALUES
(300,
 40
);

INSERT INTO #action
VALUES
(400,
 10
);

INSERT INTO #action
VALUES
(500,
 50
);


SELECT c.id_device,
       c.id_capability,
       a.id
FROM #map_device_cap c
     JOIN #action a ON c.id_capability = a.id_capability
     JOIN #map_device_action ma ON a.id = ma.id_action
                                   AND c.id_device = ma.id_device
UNION
SELECT c.id_device,
       c.id_capability,
       NULL
FROM #map_device_cap c
     LEFT JOIN #map_device_action ma ON c.id_device = ma.id_device
WHERE ma.id_device IS NULL;