subquery is not working correctly

Question

1.00/5 (1 vote)

See more:

Hii ,

SQL

select MyCountry
from
(
(select countryname from Country) MyCountry
)a

This type of query i am trying to generate as per my requirement i tried this ..

but for some reason i am geeting an error .. m i doing something wrong ??

please suggest

Posted 14-Oct-14 18:35pm

Torakami

Add a Solution

Comments

Arora_Ankit 15-Oct-14 1:15am

you want one subquery for each column. And multiple columns at a time?

Mukund_Narayan 15-Oct-14 2:04am

what error are you getting exactly? And which dbms are you using ? is it oracle, sqlserver, mysql what? As I know in Sqlserver this syntax is not valid.

4 solutions

Solution 1

SQL

select [location]
from [dbo].[department] WHERE
DEPT_NO IN (select [dept_no] from department )

you try like this with out where condition how can you execute sub query....

Posted 14-Oct-14 18:38pm

Burning Thoughts

Updated 14-Oct-14 18:39pm

v2

Comments

Torakami 15-Oct-14 0:44am

ytes thanks for this , iam agrre with this as well , but i want to generate many columns like i have seen yu above ...please correct me that ...

Burning Thoughts 15-Oct-14 0:48am

can you explain your requirement more

Torakami 15-Oct-14 0:53am

on the top i will mention all my required column names (eitable) and from the inner query which will be complate subquery , and each outer column will map to my inner select column s

Solution 2

SQL

Try this one,
select a.column1 as Mycountry1,a.column2 as Mycountry2,a.column3 as Mycountry2
from
(
(select column1,column2,column3 from table))a

Posted 14-Oct-14 18:53pm

Rajesh waran

Comments

Torakami 15-Oct-14 1:02am

nopes brother , this is not working ,,the one i have provided one is the synatx i have seen many times , but i am making somwhwere mistake there ...

Torakami 15-Oct-14 1:03am

you upper select column name is automatically mapped with inner column names with upper alices

Rajesh waran 15-Oct-14 1:17am

not yet clear with ur needs,Based on my observation,U tried to bring out the subquery columns to upper select column so that i corrected ur query as per ur needs,i'm not sure that u are be clear with ur question.

Rajesh waran 15-Oct-14 1:31am

try solution 3, if u are trying to do like that

Torakami 15-Oct-14 4:07am

yeh ,.. i have accepted that solution

Solution 6

select MyCountry
from
(select countryname MyCountry from Country) a

Posted 14-Oct-14 21:46pm

MokarramNazri

Comments

Rajesh waran 15-Oct-14 3:52am

You are right,i have provided same solution for multiple column,the question is not yet corrected.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arora_Ankit · Accepted Answer · 2014-10-14T19:21:00

Solution 3

if you want one subquery for each column And multiple columns at a time then use like this

SQL

select MyCountry,MyCountry1
from
(
SELECT (select countryname from Country) AS MyCountry,
       (select countryname from Country) AS MyCountry1
)a

But in this case you need to have where clause in the subquery, so that it will return one value.

Posted 14-Oct-14 19:21pm

Arora_Ankit

Comments

Torakami 15-Oct-14 1:31am

There you go man , i forgot to use one more select inside my iner subquery ... thanks

Arora_Ankit 15-Oct-14 2:57am

Welcome

jaket-cp 15-Oct-14 5:07am

If this is what your require, there is no need for the outer select wrapper.
select
(select countryname from Country where clause) AS MyCountry,
(select countryname from Country where clause) AS MyCountry1
Will do the same job.