Query Help - 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 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2014-01-30 : 00:00:04

Please ignore my earlier post..

I need a query which should update the master table with status based
on the action columns values.

If any of the values of the tableB for action column are 1,3,4,5,6 then update the status to 'Y' for the corresponding
SID in the tableA else the SID will be 'N'.

Below is the sample data with expected output.

Thanks for your help in advance..


TableA

SID    Name
----   -----
10      Andy
11      Sam
12      pat
13      Mat
14      John

TableB

SID   action 
----  ------- 
10      1
10      3
10      9
11      5
11      6
12      2
12      7
13      1
14      10

Expected Output:

SID    Name    status
----   -----  -------
10      Andy    Y
11      Sam     Y
12      pat     N
13      Mat     Y
14      John    N

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-01-30 : 01:42:03

[code]

;with TableA
AS(
select 10 as SID, 'Andy' as Name union all
select 11 ,'Sam' union all
select 12,'Pat' union all
select 13,'Mat' union all
select 14,'John'
)
,TableB
as (
select 10 as SID,1 as action union all
select 10,3 union all
select 10, 9 union all
select 11, 5 union all
select 11, 6 union all
select 12, 2 union all
select 12, 7 union all
select 13, 1 union all
select 14, 10)

select
A.SID
,A.Name
,case when sum(valStatus) > 0 then 'Y' else 'N' end as status
from
(
select
SID
,action
, case when action in (1,3,4,5,6) then 1 else 0 end as valStatus
from
TableB)B
inner join TableA as A on A.SiD=B.SID
Group by
A.SID
,A.Name

[/code]

output:
[code]
SID Name status
10 Andy Y
11 Sam Y
12 Pat N
13 Mat Y
14 John N
[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-30 : 06:57:22

[code]
SELECT a.*,CASE WHEN b.SID IS NULL THEN 'N' ELSE 'Y' END
FROM TableA a
LEFT JOIN (SELECT SID
FROM TableB
GROUP BY SID
HAVING SUM(CASE WHEN action IN (1,3,4,5,6) THEN 1 ELSE 0 END) > 0
)b
ON b.SID = a.SID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-30 : 16:13:18

[code]SELECT a.[SID],
a.Name,
CASE
WHEN b.[SID] IS NULL THEN 'Y'
ELSE 'N'
END AS [Status]
FROM TableA AS a
LEFT JOIN (
SELECT [SID]
FROM TableB
WHERE [Action] IN (1, 3, 4, 5, 6)
GROUP BY [SID]
) AS b ON b.[SID] = a.[SID];[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources