Please Help me to Write/construct this query?

[happyIndian100]

Please Help!
I have two tables named category and Products which has the following data:-

category :
-----------
catid ParentCode CatCode
-----------------------------
1 IT CATIT
2 IT ABCIT
3 IT HPIT
4 PRN COMPRN
5 CDD SRRCD

Products:
--------
ID CatCodeDet
-----------------
1001 1-CATIT
1002 3-HPIT
1003 5-SRRCD
1004 4-COMPRN
1005 3-HPIT

now i want all the Products where parentcode is IT.
the result should be like:

ID CatCodeDet
-----------------
1001 1-CATIT
1002 3-HPIT
1005 3-HPIT

Please help.

Thx in advance...

 

[dbomrrsm]

SELECT [ID],CatCodeDet
FROM PRODUCTS
WHERE LEFT(CatCodeDet,1) IN
(SELECT catid from category where ParentCode = 'IT')

DBomrrsm

 

[happyIndian100]

Thank You very much DBomrrsm.
It was perfect.

i made a bit of change if the catCodeDet is 2 or more digits infront like (150-HPIT or 25-HPIT).

so i used,
substring(CatCodeDet,1,patindex('%-%',CatCodeDet)-1) in place of LEFT(CatCodeDet,1).

thx once again.
Have a Nice Day.

 

[dbomrrsm]

Always glad to make an Indian happy

DBomrrsm