case 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

SQL Server 2005 Forums

Transact-SQL (2005)

case query

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-02-03 : 05:46:31

Hi,

I need to write a sql query joining 2 tables.
The first table is called IncidentIdStatus and contains 2 columns:
statusid and status
1 open
2 close
3 pending

Table MsgInfoExternals contains the following
instance propvalue
26 open
34 pending
45 close
37 close

I need a query that will say that if the propvalue is pending then return only statusid 2,3
if it's close then return only 3
if open return all

how can i write such a query?

thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:16:49

[code]SELECT m.instance,m.propvalue,i.status
FROM MsgInfoExternals m
CROSS JOIN IncidentIdStatus i
WHERE ((m.propvalue='pending' AND i.statusid in (2,3))
OR (m.propvalue='close' AND i.statusid =3)
OR (m.propvalue='open'))[/code]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-02-03 : 07:39:46

Thanks that works wonderful

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources