[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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
0
ayha1999
Asked:
ayha1999
1 Solution
 
jerrypdCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now