SELECT from two tables, JOIN without relationship, repeating val

ghz 8months ago ⋅ 102 views

SELECT from two tables, JOIN without relationship, repeating value in one col or the other

I have two SELECT statements that acquire the correct data from two different tables.

-- 1: 
SELECT employee_id FROM employees
WHERE city = 'London';

-- returns employee_id = 5, 6, 7, 9
-- 2:
SELECT category_id FROM categories
WHERE category_name ILIKE 'dairy%';

-- returns category_id = 4

I have an employees_categories table I'd like to populate with the following, based on the above criteria:

    | employee_id | category_id |
    | ----------- | ----------- | 
    |           5 |           4 |
    |           6 |           4 |
    |           7 |           4 |
    |           9 |           4 |

I could use

INSERT INTO employees_categories (
    employee_id, category_id
) VALUES (5,4), (6,4), (7,4), (9,4);

But I was hoping to create a CTE based on the two SELECT statements at the top, so I can use it for INSERT INTO (automate the entire process in one query).

I am having trouble figuring out how to pair results from the two SELECTs, since employee_id or category_id potentially repeat (as shown), and there is no common relationship between the two tables they are selected from.

Any help greatly appreciated!

Answers

You can achieve this by using a Cartesian product (also known as a cross join) between the results of the two SELECT statements. Here's how you can do it:

WITH EmployeeIDs AS (
    SELECT employee_id FROM employees
    WHERE city = 'London'
),
CategoryIDs AS (
    SELECT category_id FROM categories
    WHERE category_name ILIKE 'dairy%'
)
INSERT INTO employees_categories (employee_id, category_id)
SELECT e.employee_id, c.category_id
FROM EmployeeIDs e
CROSS JOIN CategoryIDs c;

In this query:

  • The CTEs EmployeeIDs and CategoryIDs capture the results of the two SELECT statements individually.
  • The main query performs a Cartesian product (cross join) between the results of EmployeeIDs and CategoryIDs, pairing each employee_id with each category_id.
  • The results of the Cartesian product are then inserted into the employees_categories table.

This approach automates the process in one query, allowing you to achieve your desired outcome efficiently.

#sql