Joins - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Joins

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-10 : 00:47:19

[code]DECLARE @Tempfun1 TABLE
(
col1 CHAR(1),
col2 INT
)
INSERT INTO @Tempfun1
SELECT 'A', 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 4 UNION ALL
SELECT 'B', 1 UNION ALL
SELECT 'B', 2 UNION ALL
SELECT 'B', 3 UNION ALL
SELECT 'B', 4

DECLARE @Tempfun2 TABLE
(
col2 INT
)
INSERT INTO @Tempfun2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3[/code]

How to get the following
A 1
A 2
A 3
B 1
B 2
B 3
B 4
the condition are when col1=B, select all B but when col1=A, select matched record between 2 table
<- having flu

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-10 : 00:50:09

can it be done without subquery?

SELECT	*
FROM	(
		SELECT	col1, CASE WHEN col1='A' then t2.col2 ELSE t1.col2 END col2
		FROM	@Tempfun1 t1 left join @Tempfun2 t2 on t1.col2=t2.col2
		)a
WHERE	col2 IS NOT NULL

Hope can help...but advise to wait pros with confirmation...

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-10 : 01:16:46

SELECT DISTINCT T1.COL1,T1.COL2 FROM @TEMPFUN1 T1
INNER JOIN @TEMPFUN2 T2 ON T1.COL2 = ( CASE WHEN T1.COL1 = 'A' THEN T2.COL2 ELSE T1.COL2 END)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 01:19:12

try like this


SELECT	distinct col1, isnull(t2.col2,t1.col2)
FROM	@Tempfun1 t1 left join @Tempfun2 t2 on t2.col2 = CASE WHEN col1='A' then t2.col2 ELSE t1.col2 END

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-10 : 01:49:31

wow thx thx ^^

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 01:53:16

welcome

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-10 : 02:28:55

[code]DECLARE @Tempfun1 TABLE
(
col1 CHAR(1),
col2 INT,
col3 CHAR(3)
)
INSERT INTO @Tempfun1
SELECT 'A', 1, 'PDO' UNION ALL
SELECT 'A', 2, 'GRO' UNION ALL
SELECT 'A', 3, 'PDO' UNION ALL
SELECT 'A', 4, 'GRO' UNION ALL
SELECT 'B', 1, 'PDO' UNION ALL
SELECT 'B', 2, 'GRO' UNION ALL
SELECT 'B', 5, 'PDO' UNION ALL
SELECT 'B', 4, 'GRO'

DECLARE @Tempfun2 TABLE
(
col2 INT
)
INSERT INTO @Tempfun2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3[/code]

how to get the following
A 1
A 2
A 3
A 4
B 1
B 2
B 4
where the condition are PDO(join), GRO(left join)

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 02:44:16

SELECT distinct col1, t1.col2
FROM @Tempfun1 t1 join @Tempfun2 t2 on t2.col2 = t1.col2
and t1.col3 = 'pdo'

union

SELECT distinct col1, t1.col2
FROM @Tempfun1 t1 left join @Tempfun2 t2 on t2.col2 = t1.col2
where t1.col3 = 'gro'

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-10 : 02:45:46

quote:
Originally posted by waterduck

wow thx thx ^^

Hope can help...but advise to wait pros with confirmation...

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources