I have a query
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = #ID#;
if #ID# = 1 it returns table
GUID | word
-----------
1 | A
2 | B
3 | C1
3 | C2
4 | D1
4 | D2
5 | E
if #ID# = 2 it returns table
GUID | word
-----------
1 | AA
2 | BB1
2 | BB2
3 | CC
4 | DD1
4 | DD2
6 | FF
Now I want merge these two tables/queries to get a table which looks like that:
GUID | word1 | word2
--------------------
1 | A | AA
2 | B | BB1
2 | B | BB2
3 | C1 | CC
3 | C1 | CC
4 | D1 | DD1
4 | D1 | DD2
4 | D2 | DD1
4 | D2 | DD2
So basically it is a table of the Cartesian product of the rows with the same GUID
You don't need subqueries:
SELECT d1.GUID, w1.word, w2.word
FROM dictionary d1 JOIN
word w1
ON w1.ID = d1.ID_word JOIN
dictionary d2
ON d2.GUID = d.GUID JOIN
word w2
ON w2.id = d2.ID_word
WHERE w1.id_language = ? AND
w2.id_language = ?;
I think this is more readable using CTEs:
WITH dw AS (
SELECT d.GUID, w.word, w.id_language
FROM dictionary d JOIN
word w
ON w.ID = d.ID_word
)
SELECT dw1.GUID, dw1.word, dw2.word
FROM dw dw1 JOIN
dw dw2
ON dw1.GUID = dw2.GUID
WHERE dw1.id_language = ? AND
dw2.id_language = ?;
In both these examples (and what is implied by your question) GUIDs with only one word are filtered out. These can be included by tweaking the queries.
select *
from
( -- if #ID# = 1 it returns table
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) as t1
join
( -- if #ID# = 2 it returns table
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 2
) as t2
-- Cartesian product of the rows with the same GUID
on t1.guid = t2.guid
You can just JOIN your two queries on GUID:
SELECT q1.GUID, q1.word AS word1, q2.word AS word2
FROM (
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) q1
JOIN (
SELECT
d.GUID, w.word
FROM
dictionary d
JOIN
word w ON w.ID = d.ID_word
WHERE
w.id_language = 1
) q2 ON q2.GUID = q1.GUID