concatenate 2 query - 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

concatenate 2 query

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 06:55:33

I have to collect the info of those people who belongs to SBI and ICICI Bank using only one query.
But SBI holder having Account no. length=11 and
ICICI having account no=12
I have two query which is seperately working for these banks and it is easy to retrieve value from program.

select aid,vacctno from members where vastBank ='State Bank of India' and datalength(vacctno)=11

select aid,vacctno,vastbank from members where vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12

it gives me alist of user .
If I have to find for any particular user as
aid='U1'
then what would be query

Ved Prakash Jha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 07:22:31

select * from
(
select aid,vacctno from members where vastBank ='State Bank of India' and datalength(vacctno)=11
union all
select aid,vacctno,vastbank from members where vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12
) as t
where aid='U1'

Madhivanan

Failing to plan is Planning to fail

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 07:31:31

it gives an error as

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Ved Prakash Jha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 07:35:13

select * from
(
select aid,vacctno,vastbank from members where vastBank ='State Bank of India' and datalength(vacctno)=11
union all
select aid,vacctno,vastbank from members where vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12
) as t
where aid='U1'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 07:36:44

or simply

select aid,vacctno,vastbank from members
where (vastBank ='State Bank of India' and datalength(vacctno)=11) or (vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12)
where aid='U1'

Madhivanan

Failing to plan is Planning to fail

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 08:25:35

This query doesnot give output:

select * from
(
select aid,vacctno,vastbank from members where vastBank ='State Bank of India' and datalength(vacctno)=11
union all
select aid,vacctno,vastbank from members where vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12
) as t
where aid='U100000000'

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 08:28:09

this query wrong output:

select aid,vacctno,vastbank from members
where (vastBank ='State Bank of India' and datalength(vacctno)=11) or (vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12)

and aid='U100000000'

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 08:29:55

select aid,vacctno,vastbank from members
where (vastBank ='State Bank of India' and datalength(vacctno)=11) or (vastBank ='Icici Bank Ltd.' and datalength(vacctno)=12)

and aid='U100000000'

this query returns only one row but it gives more than one row

Ved Prakash Jha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 09:21:02

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

vedjha
Posting Yak Master

228 Posts

Posted - 2008-09-01 : 09:44:28

AID AccountNo Bank

U140 99192929699 State Bank of India
U141 91324394329 State Bank of India
U142 99199893939 State Bank of India

Ved Prakash Jha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 10:20:03

So you dont have data for aid='U100000000'?

Madhivanan

Failing to plan is Planning to fail

shanmp
Starting Member

13 Posts

Posted - 2008-09-02 : 07:35:42

The result which you have posted shows only SBI and not the ICICI account. Please post the sample data and the expected result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources