Tengo una consulta
SELECT d.GUID, w.word FROM dictionary d JOIN word w ON w.ID = d.ID_word WHERE w.id_language = #ID#; si #ID# = 1 devuelve tabla
GUID | word ----------- 1 | A 2 | B 3 | C1 3 | C2 4 | D1 4 | D2 5 | E si #ID# = 2 devuelve tabla
GUID | word ----------- 1 | AA 2 | BB1 2 | BB2 3 | CC 4 | DD1 4 | DD2 6 | FFAhora quiero fusionar estas dos tablas/consultas para obtener una tabla que se vea así:
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 | DD2Entonces, básicamente, es una tabla del producto cartesiano de las filas con el mismo GUID
No necesitas subconsultas:
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 = ?;Creo que esto es más legible usando CTE:
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 = ?;En ambos ejemplos (y lo que implica su pregunta), se filtran los GUID con una sola palabra. Estos se pueden incluir ajustando las consultas.
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.guidPuede JOIN a sus dos consultas en 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