sql command

I have a table and data as follow.

Id(PK-Identity)   ReqId(FK) Desc                wCode
1                        1001         some desc       W
2                        1001         some desc       M
3                        1001         some desc       C
4                        1002         some desc       M
5                        1003         some desc       W

How can select all records from this table related to wCode 'W' and other records where ReqId is same for other records. In the above example all records should display except Id 4.

please help.

thanks
LVL 7
ayha1999Asked:
Who is Participating?
 
jerrypdConnect With a Mentor Commented:
try this?

select * from <table> where reqid in (select reqid from <table> as tab2 where tab2.wCode = 'W');

0
 
Ephraim WangoyaCommented:

try
;with CTE
as
(
  select ReqId, wCode, COUNT(ReqId) cnt
  from Table1
  group by ReqId, wCode
 )   
 
 select * 
 from Table1 
 inner join CTE A on A.ReqID = Table1.ReqId
 where A.wCode = 'W'
 or A.cnt > 1

Open in new window

0
 
Ephraim WangoyaCommented:
this
;with CTE
as
(
  select ReqId, wCode, COUNT(ReqId) cnt
  from Table1
  group by ReqId, wCode
 )   
 
 select Table1.* 
 from Table1 
 inner join CTE A on A.ReqID = Table1.ReqId
 where A.wCode = 'W'
 or A.cnt > 1

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Select * from table where wCode = 'W' or ID in (Select ID from table where wCode = 'W')
0
 
ayha1999Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.