Select several subqueries with more than 1 value

Question

Post reply

Select several subqueries with more than 1 value

masoudk1990

SSCommitted

Points: 1651
More actions
September 12, 2013 at 1:57 am

#277965

Hi. There is no problem with this query
select (select 'hello'), (select 'world')
But if one select statement return more than 1 value it throw exception.
Initialization:
Create table table1(c nvarchar(1));
Create table table2(c nvarchar(1));
insert into table1 ('1')
insert into table1 ('2')
insert into table1 ('3')
insert into table2 ('a')
insert into table2 ('b')
and here is query that generate error:
select (select c from table1), (select c from table2)
I expect output be something like this:
c,c
------
1,a
2,b
3,null
It seems impossible, but I want to be sure if there is any trick for it or not.
Thank you for help.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your initialisation code fails with an error. Your expectations are incorrect.

I think this is what you are looking for:

CREATE TABLE #table1(c nvarchar(1));

CREATE TABLE #table2(c nvarchar(1));

INSERT INTO #table1 SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3';

INSERT INTO #table2 SELECT 'a' UNION ALL SELECT 'b';

WITH

Table1Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table1),

Table2Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table2)

SELECT t1.c, t2.c

FROM Table1Sequenced t1

LEFT JOIN Table2Sequenced t2 ON t2.rn = t1.rn;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Loundy SSCertifiable Points: 6080 More actions · Answer 2

this is what I came up with from your existing structure (after making the insert statements work)

WITH cte

AS

(

SELECTc

,ROW_NUMBER() OVER(ORDER BY c) AS rowid

FROM table1

),

cte2

AS

(

SELECT c

,ROW_NUMBER() OVER(ORDER BY c) AS rowid

FROM Table2

)

SELECT c1.c, c2.c

FROM cte c1

LEFT JOIN cte2 c2

ON c1.rowid = c2.rowid;

However there was no relationship between the 2 tables, the values have no meaning. This statement literally numbers the rows and joins them together....

[font="Times New Roman"]There's no kill switch on awesome![/font]

TomThomson SSC Guru Points: 104773 More actions · Answer 3

ChrisM@Work (9/12/2013)
Your initialisation code fails with an error. Your expectations are incorrect.
I think this is what you are looking for:
CREATE TABLE #table1(c nvarchar(1));
CREATE TABLE #table2(c nvarchar(1));
INSERT INTO #table1 SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3';
INSERT INTO #table2 SELECT 'a' UNION ALL SELECT 'b';
WITH
Table1Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table1),
Table2Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table2)
SELECT t1.c, t2.c
FROM Table1Sequenced t1
LEFT JOIN Table2Sequenced t2 ON t2.rn = t1.rn;

But is it certain that there will always be at least as many rows in #Table1 as in #Table2? If not, the select component of the statement needs to change to take account of this, the LEFT JOIN should be a FULL OUTER JOIN.

Tom

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

L' Eomot Inversé (9/12/2013)
ChrisM@Work (9/12/2013)
Your initialisation code fails with an error. Your expectations are incorrect.
I think this is what you are looking for:
CREATE TABLE #table1(c nvarchar(1));
CREATE TABLE #table2(c nvarchar(1));
INSERT INTO #table1 SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3';
INSERT INTO #table2 SELECT 'a' UNION ALL SELECT 'b';
WITH
Table1Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table1),
Table2Sequenced AS (SELECT c, rn = ROW_NUMBER() OVER(ORDER BY c) FROM #table2)
SELECT t1.c, t2.c
FROM Table1Sequenced t1
LEFT JOIN Table2Sequenced t2 ON t2.rn = t1.rn;
But is it certain that there will always be at least as many rows in #Table1 as in #Table2? If not, the select component of the statement needs to change to take account of this, the LEFT JOIN should be a FULL OUTER JOIN.

Good point, Tom, thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

masoudk1990 SSCommitted Points: 1651 More actions · Answer 5

Thank you every one.

And sorry for errors in insert statement.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.