I need to get the data from 2 derived tables in one row if possible.
Schema (MySQL v8.0)
create table users (
userId INT
);
INSERT INTO users (userId) VALUES (1);
Query #1
SELECT userId, firstTable.name, secondTable.text
FROM users
INNER JOIN (
VALUES
ROW('FirstName'),
ROW('secondName')
) firstTable(name)
INNER JOIN (
VALUES
ROW('Some Text'),
ROW('Some other text')
) secondTable(text)
WHERE userId = 1;
| userId | name | text |
|---|---|---|
| 1 | secondName | Some Text |
| 1 | FirstName | Some Text |
| 1 | secondName | Some other text |
| 1 | FirstName | Some other text |
This query returns a Cartesian product (I think) but I need to return the data in one row, or rather one row for every pair of values in the derived tables this:
| userId | name | text |
|---|---|---|
| 1 | FirstName | Some Text |
| 1 | SecondName | Some other text |
>Solution :
You must add a column which sets the relation between the rows.
CREATE TABLE users ( userId INT ) SELECT 1 userId;
SELECT userId, firstTable.name, secondTable.text
FROM users
CROSS JOIN (
VALUES
ROW(1, 'FirstName'),
ROW(2, 'secondName')
) firstTable(id, name)
INNER JOIN (
VALUES
ROW(1, 'Some Text'),
ROW(2, 'Some other text')
) secondTable(id, text) USING (id)
WHERE userId = 1;
| userId | name | text |
|---|---|---|
| 1 | FirstName | Some Text |
| 1 | secondName | Some other text |