is this possible to do using join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

is this possible to do using join?

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 02:50:17

hi,
i have query like this:
select userid, convert(varchar(20),user_date) as userdate,table_name,operation from HRUser_developerlog where operation='insert'

result:
userid userdate table operation
1 Jan 24 2007 11:47AM usermaster insert

and i want to uses queries :

if exists(select null from usermaster where userid = 1)
select 'found'as result
else
select 'not found'
if exists(select null from menumaster where userid = 1)
select 'found'as result1
else
select 'not found'

if exists(select null from rolemaster where userid = 1)
select 'found'as result2
else
select 'not found'

then i need a result like this:

userid userdate table operation
1 Jan 24 2007 11:47AM usermaster insert
result result1 result2
found not found found

likee this, how to join the firstquery result with other query results.please any one help me to do.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 03:22:50

please any one can answer me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:07:26

I was going to ask what you have done this far, but I realized you haven't.

SELECT	CASE
			WHEN EXISTS (select null from usermaster where userid = 1) THEN 'found'
			ELSE 'not found'
		END as result,
		CASE
			WHEN EXISTS (select null from menumaster where userid = 1) THEN 'found'
			ELSE 'not found'
		END as result1,
		CASE
			WHEN EXISTS (select null from rolemaster where userid = 1) THEN 'found'
			ELSE 'not found'
		END as result2

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 04:15:54

hi peso i need to join the results with the following

userid userdate table operation result1 result2 result3
1 Jan 242007 11:47AM usermaster insert found found found

so how to jon the select case which u shown with the following

select userid, convert(varchar(20),user_date) as userdate,table_name,operation from HRUser_developerlog where operation='insert'

i want to join this qwery with the following

SELECT CASE
WHEN EXISTS (select null from usermaster where userid = 1) THEN 'found'
ELSE 'not found'
END as result,
CASE
WHEN EXISTS (select null from menumaster where userid = 1) THEN 'found'
ELSE 'not found'
END as result1,
CASE
WHEN EXISTS (select null from rolemaster where userid = 1) THEN 'found'
ELSE 'not found'
END as result2

so i need to get result like this:

userid userdate table operation result1 result2 result3
1 Jan 242007 11:47AM usermaster insert found found found

now please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:31:13

Still don't get it?

select	userid,
		convert(varchar, user_date, 103) as userdate,
		table_name,
		operation,
		CASE
			WHEN EXISTS (select null from usermaster as um where um.userid = dl.userid) THEN 'found'
			ELSE 'not found'
		END as result,
		CASE
			WHEN EXISTS (select null from menumaster as mm where mm.userid = dl.userid) THEN 'found'
			ELSE 'not found'
		END as result1,
		CASE
			WHEN EXISTS (select null from rolemaster as rm where rm.userid = dl.userid) THEN 'found'
			ELSE 'not found'
		END as result2
from		HRUser_developerlog as dl
where		dl.operation = 'insert'

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 05:12:47

thanks peso, i got nice reply

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 05:21:59

This might perform better

select distinct	userid,
		convert(varchar, user_date, 103) as userdate,
		table_name,
		operation,
		CASE WHEN um.userid is null THEN 'not found' ELSE 'found' end as result,
		CASE WHEN mm.userid is null THEN 'not found' ELSE 'found' end as result1,
		CASE WHEN rm.userid is null THEN 'not found' ELSE 'found' end as result2
from		HRUser_developerlog as dl
left join	usermaster as um where um.userid = dl.userid
left join	menumaster as mm where mm.userid = dl.userid
left join	rolemaster as rm where rm.userid = dl.userid
where		dl.operation = 'insert'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources