List based on specific category

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

General SQL Server Forums

New to SQL Server Programming

List based on specific category

Author

Topic

gustho
Starting Member

2 Posts

Posted - 2011-02-24 : 22:11:57

Hello Folks,
I have a question, I will example with example :

Inv# cat
lcs01 ms010
lcs01 mc015
lcs01 me025
lcs02 md001
lcs02 md020
lcs02 mc001
lcs03 ma020
lcs03 mc010

The desired result is as follows :
Inv# cat
lcs01 ms010
lcs01 mc015
lcs01 me025
lcs03 ma020
lcs03 mc010

If the MS010 or ma001 exist, we need to show all the lines related to that inv#. In the above example lcs01 will be shown 3 because Ms010 appears once in that inv series and lcs03 appears 2 times because ma010 appears in the inv series .

I hope I have explained this correctly.
thanks

Gus.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 22:25:24

[code]
select *
from yourtable t
where exists
(
select *
from yourtable x
where x.inv = t.inv
and x.cat in ('MS010', 'MA001')
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-26 : 09:34:58

quote:

Originally posted by khtan


select *
from   yourtable t
where  exists
       (
           select *
           from   yourtable x
           where  x.inv = t.inv
           and    x.cat in ('MS010', 'MA001'))

KH
[spoiler]Time is always against us[/spoiler]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-26 : 10:11:06

thanks for the correction

KH
[spoiler]Time is always against us[/spoiler]

gustho
Starting Member

2 Posts

Posted - 2011-02-26 : 15:28:39

Perfect !! Appreciate it.

quote:

Originally posted by khtan


select *
from   yourtable t
where  exists
       (
           select *
           from   yourtable x
           where  x.inv = t.inv
           and    x.cat in ('MS010', 'MA001')
       )

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources