Multiple Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Select Statement

Author

Topic

mimran
Starting Member

3 Posts

Posted - 2010-12-07 : 02:49:49

I want a single Select statement which containe multiple select statements. For example i have two independent select statements;

select col1 from table1;
select col2 from table2;

Now i want to execute above two select statements into single select statement, such that i could have single view containing two columns

Col1 Col2

Can anybody help me with example other than this one select col1, col2 from table1, table2?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 03:17:04

Is there any common column to join two tables ?
What if both table does not have same number of rows ?

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 03:19:13

If dont have answer for both the question then go with below query -


SELECT A.Col1, B.Col1 FROM 
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) RowNo, Col1 FROM Table1 
) A 
FULL OUTER JOIN 
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) RowNo, Col1 FROM Table2
) B ON A.RowNo = B.RowNo

Vaibhav T

If I cant go back, I want to go fast...

mimran
Starting Member

3 Posts

Posted - 2010-12-07 : 03:56:17

Both tables don't have any common column. If two tables have not same no of rows, extra rows should be displayed. Can we do this without applaying join on two tables ? like dummy example : Select ( select col1 from table1, select col2 from table2)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 04:04:29

quote:
Originally posted by mimran

Select ( select col1 from table1, select col2 from table2)

This Query is not possible in SQL Server.
It can be modified according to SQL Server Syntax.

See my reply
It will gived desired output.
there is not any way to do it without join as per my knowledge.

Vaibhav T

If I cant go back, I want to go fast...

mimran
Starting Member

3 Posts

Posted - 2010-12-07 : 04:07:54

Thanks Vaibhav for your quick reply.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 04:24:10

Welcome

Vaibhav T

If I cant go back, I want to go fast...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 04:29:48

quote:
This Query is not possible in SQL Server.

Well...not entirely true. Although there needs to be some changes and there are some limitations. This will work just fine but each sub-select can only return one single value:

Select 
  Col1 = (select col1 from table1 where id = 1), 
  Col2 = (select col2 from table2 where id = 2)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 05:27:29

quote:
Originally posted by Lumbago

quote:
This Query is not possible in SQL Server.
Well...not entirely true. Although there needs to be some changes and there are some limitations. This will work just fine but each sub-select can only return one single value:
Select
Col1 = (select col1 from table1 where id = 1),
Col2 = (select col2 from table2 where id = 2)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

This will not fulfill the requirement as such.

Vaibhav T

If I cant go back, I want to go fast...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 05:37:14

quote:
This will not fulfill the requirement as such.

Well...actually it does, nothing is said about how many rows is supposed to be returned. But I agree that this is probably not what the OP had in mind...you're full outer join is probably more suited.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 05:51:46

quote:
Originally posted by Lumbago

quote:
This will not fulfill the requirement as such.
nothing is said about how many rows is supposed to be returned.

I asked these questions to OP
See the reply of OP.

If two tables have not same no of rows, extra rows should be displayed.

Vaibhav T

If I cant go back, I want to go fast...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 06:00:04

Hm...you're right. Sorry.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-07 : 06:01:46

quote:
Originally posted by Lumbago

Hm...you're right. Sorry.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

No need to be sorry, I just wanted to be exact.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources