"*=" syntax problem - 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)

"*=" syntax problem

Author

Topic

bntconan
Starting Member

3 Posts

Posted - 2009-06-08 : 22:08:10

Hi all,

I got three tables:
declare @tbl1 table(id char(10), name char(10))
insert @tbl1 select
'1','a' union select
'2','b' union select
'33','d' union select
'44','e'

declare @tbl2 table(id char(10), name char(10))
insert @tbl2 select
'1','a' union select
'2','b' union select
'55','d' union select
'66','e'

declare @tbl3 table(id char(10), name char(10))
insert @tbl3 select
'1','a' union select
'2','b' union select
'55','d' union select
'33','d' union select
'88','f' union select
'99','g'

And I have a query like this:

Select * from @tbl1 a, @tbl2 b, @tbl3 c
Where a.id *= b.id
and c.id *= b.id

The results return 24 rows.

Now my task is to rewrite such query so that we not to use "*=" anymore. For my understanding, if A.xxx *= B.xxx, meaning that Table A left join Table B...

So I rewrite like this:

Select * from @tbl1 a
left join @tbl2 b on a.id=b.id
right join @tbl3 c on c.id=b.id

But the result only return 6 rows.

I have tried to solve out this for a week but cannot find a solution. Please help, thanks a lot!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-08 : 22:16:54

[code]
SELECT *
FROM (@tbl2 b right outer join @tbl3 c on b.id=c.id)right outer join @tbl1 a on b.id=a.id
[/code]
like this??

bntconan
Starting Member

3 Posts

Posted - 2009-06-08 : 22:25:12

quote:
Originally posted by waterduck

SELECT *
FROM (@tbl2 b right outer join @tbl3 on b.id=c.id)right outer join @tbl1 on b.id=a.id

like this??

Thanks for reply!

I have tried to run it, but it only return 4 rows, not the same results (24 rows) as the original one...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-08 : 22:47:23

sorry i dunno how to help u...shd b left outer join but dunno y cant match urs query

bntconan
Starting Member

3 Posts

Posted - 2009-06-09 : 03:21:18

Got it finally. The third table should use CROSS JOIN.

SELECT A.id, A.name,
CASE WHEN B.id = C.id
THEN B.id
END AS b_id,
CASE WHEN B.id = C.id
THEN B.name
END AS b_name,
C.id AS c_id, C.name AS c_name
FROM @tbl1 AS A
LEFT JOIN @tbl2 AS B
ON A.id = B.id
CROSS JOIN @tbl3 AS C;

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources