CODEWITHSUNDEEP
sqljoincorrelated-subquery
user8907896
user8907896

Reputation: 71

Merge multiple row subquery into corelated SQL query

I have a correlated SQL sub query which returns multiple rows. For instance,

SELECT NAME AS NAME1,
(SELECT NAME FROM ...) AS NAME2 /*this inner select returns more than one rows*/ 
FROM PERSONAL_INFORMATION

Is it possible to merge the rows returned by NAME2 with rest of the main query, here NAME1, that is. What I am thinking is kind of a cross product shown below where I can group all the given instances using GROUP BY or DISTINCT?

NAME1_1  NAME2_1
NAME1_1  NAME2_2
NAME1_2  NAME2_1
NAME1_2  NAME2_2

Upvotes: 0

Views: 229

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Just use CROSS JOIN then.

SELECT I1.NAME AS NAME1, I2.NAME AS NAME2 
FROM PERSONAL_INFORMATION I1
CROSS JOIN PERSONAL_INFORMATION2 I2;

If you need more detail, you may use GROUP BY :

SELECT I1.NAME AS NAME1, I2.NAME AS NAME2, 
       COUNT(1) cnt, SUM(COL2) sum_col1 ...
FROM PERSONAL_INFORMATION I1
CROSS JOIN PERSONAL_INFORMATION2 I2
GROUP BY I1.NAME, I2.NAME;

Upvotes: 1

Related Questions