t-sql - 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

t-sql

Author

Topic

h27091978
Starting Member

4 Posts

Posted - 2006-07-13 : 07:13:41

I have the follwing table structure
tableA
col 1 col2 col3
1 1 2
2 1 2

tableB
col1 col2
1 yes
2 no

now i want to fire a select statement like
select col1, col2, col3 from tableA A,tableB B where A.col1=B.Col1 ....

i expect the result set as
yes yes no
No Yes No

can it be done in a single select statement?

thankyou for help.

-Andy

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 07:26:30

[code]create table #test1
(
col1 int,
col2 int,
col3 int
)

create table #test2
(
col1 int,
col2 varchar(10)
)

insert into #test1
select 1,1, 2
union all
select 2, 1, 2

insert into #test2

select 1, 'Yes'
union all
select 2, 'No'

select (select b.col2 from #test2 b where a.col1 = b.col1) as col1, (select b.col2 from #test2 b where a.col2 = b.col1) as col2,
(select b.col2 from #test2 b where a.col3 = b.col1) as col3 from #test1 as a[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-13 : 07:26:58

I think this is not your real problem. The question above can be solved with an easy

SELECT	CASE WHEN col1 = 1 THEN 'Yes' ELSE 'No' END Col1,
	CASE WHEN col2 = 1 THEN 'Yes' ELSE 'No' END Col2,
	CASE WHEN col3 = 1 THEN 'Yes' ELSE 'No' END Col3
FROM	tableA

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 08:12:23

And another option is to just join your tables, of course...

select
    b1.col2,
    b2.col2,
    b3.col2
from tableA a
    left outer join tableB b1 on a.col1 = b1.col1
    left outer join tableB b2 on a.col2 = b2.col1
    left outer join tableB b3 on a.col3 = b3.col1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources