We help IT Professionals succeed at work.

sql command

Medium Priority
352 Views
Last Modified: 2012-06-27
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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
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

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Select * from table where wCode = 'W' or ID in (Select ID from table where wCode = 'W')

Author

Commented:
thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.