trying to write a 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

trying to write a query

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2010-12-12 : 17:51:29

I have two tables
One is named users
it has 2 fields
1. username
2. isOnline

the second is named requests
it has 3 fields
1. user1
2. user2
3. status

I need to get by one query 2 cols
1. all the usernames from the first table where isOnline = true
2. if the username (of the first table) is either user1 or user2 (of the second table) and the status is true this field will be true, if the username is not on the second table or the status is false, this field will be false.

There are no relations between the two tables.
(but I know that user1 and user2 must be listed at the first table in the username field)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-12 : 21:15:31

select t.username, case when a.user1 is null then 'false' else 'true' end
from tbl1 t
left join (select user1 from tbl2 where status = 'true' union select user2 from tbl2 where status = 'true) a
on t.username = a.user1
where t.isOnline = 'true'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elic05
Yak Posting Veteran

62 Posts

Posted - 2010-12-13 : 03:14:19

thanks, it works great
i had to fix a missing ' after the word true at the end of the third line.


SELECT  t.userName, CASE WHEN a.user1 IS NULL THEN 'False' ELSE 'True' END AS Expr1
FROM     users AS t LEFT OUTER JOIN
                   (SELECT  user1
                    FROM     requests
                    WHERE  (status = 'True')
                    UNION
                    SELECT  user2
                    FROM     requests AS requests_1
                    WHERE  (status = 'true')) AS a ON t.userName = a.user1
WHERE  (t.isOnline = 'True')

btw
it is very complicated
can it be written in a more simple way?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources