Select query help - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select query help

Author

Topic

artieman1
Starting Member

1 Post

Posted - 2007-03-08 : 00:25:04

I am attempting to find some way to select multiple rows in a table that will filter out some of the contents. Here goes my explanation.

Using the following as the table schema.

Resource|GrpName

Multiple Resources that can repeat and Multiple Grp Names that can repeat. The report requestor will select weather or not we are going to select all GrpNames or just a few.

What I would like to do is make sure that if 2 or 3 or more group names are selected, we would only list the resources that belong to all selected groups.

I can not think of how in the world I would filter on this.

select resource from <tablename> where <Selected Group Names ALL exist>

If there is a Resource that is selected that is not part of ALL the Groups selected, I do not want to see it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 00:31:43

maybe you can post your table structure, some sample data and the expected result.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 00:40:53

Is this what you want?

declare @t table
(
	resource varchar(50),
	grpname varchar(50)
)

insert @t
select 'aaa', 'grp1' union all
select 'bbb', 'grp1' union all
select 'ccc', 'grp1' union all
select 'aaa', 'grp2' union all
select 'bbb', 'grp2' union all
select 'ddd', 'grp2'

select grpname 
from @t t1 join (select 'aaa' as x union all select 'bbb' as x union all select 'ccc' as x) t2
on t1.resource = t2.x
group by grpname
having count(resource) = 3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 15:54:54

[code]
CREATE FUNCTION dbo.fnSplitText
(
@RowData varchar(4000),
@SplitOn varchar(5)
)
RETURNS @RtnValue table
(
Row int identity(1,1),
Data varchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

go

declare @t table
(
resource varchar(50),
grpname varchar(50)
)

declare @grpname varchar(100)
set @grpname='grp1,grp2'
insert @t
select 'aaa', 'grp1' union all
select 'bbb', 'grp2' union all
select 'ccc', 'grp3' union all
select 'aaa', 'grp2' union all
select 'bbb', 'grp1' union all
select 'ddd', 'grp2'

select * from @t

select resource from(
select resource from @t
where grpname in (select data from dbo.fnsplitText(@grpname,','))
)s
group by resource
having count(*)=(select count(*) from dbo.fnsplitText(@grpname,','))

go

drop function dbo.fnSplitText
go

[/code]

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources