SQL JOIN - 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 2000 Forums

Transact-SQL (2000)

SQL JOIN

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-04-29 : 02:46:13

[code]Table1
Col1
----
a
b
c
d
e

Table2
Col1
-----
a
a
b
e
e
e
e

I need the following o/p

col1, status
----------
a 1
b 1
c 0
d 0
e 1

Note:
1. O/p contains all the rows from first table
2. If the row from table1 exist in table2 then STATUS column is 1 else 0.

Thank you.[/code]

------------------------
I think, therefore I am - Rene Descartes

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-29 : 03:11:04

[code]select col1, case when countofCol1 > 0 then 1 else 0 end
from
(
select a.col1, Count(b.t1col1) AS CountOfcol1
from table1 a left join
(
select t1.col1 as t1col1, t2.col1 as t2col1
from table1 t1, table2 t2
where t1.col1 = t2.col1
) b
on a.col1 = b.t1col1
group by a.col1
) x
[/code]

PS - I believe a more correct translation of Descartes' famous expression is "I doubt therefore I am"

But don't quote me on it!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-04-29 : 03:39:56

When I executed your code i got the following error
Incorrect syntax near ')'.
After adding alias name after the last bracket it worked.
Thank you.

P.S.
Descartes himself would have loved your quote.
In fact i feel your quote is more powerful than the orginal quote!

Thanks anyway..

------------------------
I think, therefore I am - Rene Descartes

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-29 : 03:42:31

oops - cut and paste error - my apologies

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-29 : 03:47:54

Simplier way:

SELECT DISTINCT
             T1.Col1,
             status = 
             CASE     
                    WHEN t2.col1 IS NULL
                    THEN 0
                    ELSE 1
             END
FROM Table1 t1
     LEFT OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-04-29 : 03:55:31

Thanks a lot!

------------------------
I think, therefore I am - Rene Descartes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources