Forbinn Forbinn - 1 year ago 58
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 Source

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;