How do I SELECT a column that STRICTLY matches a list
-
Hello there,
I have the following table:
ROOMTYPE AMENITY
======= =======
R001 1
R001 2
R001 3
R002 1
R002 2
R002 4
R003 1
Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
Can anyone help me on this?
Kaushik
-
This:
create table MyTable (roomtype varchar(4), amenity int)
go
insert MyTable select 'R001', 1
insert MyTable select 'R001', 2
insert MyTable select 'R001', 3
insert MyTable select 'R002', 1
insert MyTable select 'R002', 2
insert MyTable select 'R002', 4
insert MyTable select 'R003', 1
go
select a.roomtype
from
( select roomtype from mytable where amenity = 1) a
inner join (select roomtype from mytable where amenity = 2) b
on a.roomtype = b.roomtype
inner join (select roomtype from mytable where amenity = 4) c
on a.roomtype = c.roomtype
and b.roomtype = c.roomtype
go
drop table MyTable
-
or:
SELECT RoomType
FROM MyTable T
JOIN (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 4
) D (Amenity)
ON T.Amenity = D.Amenity
GROUP BY RoomType
HAVING COUNT(*) = 3
-
Hello again,
Thanks for your reply but the solutions you are giving me are based on a FIXED list (always 1,2,4). But if the table contents as well as the list change during the course of time, then your queries would not help. i.e. if the table contents increases and my list becomes (2,4,5,8,9,10,...,100) then?
-
Building on Steve's, try this:
create table dbo.MyTable (roomtype varchar(4), amenity int)
go
create function dbo.RoomTypes(
@AmenityList varchar(8000) -- varchar(max) -- for SQL Server 2005
)
returns @roomtypes table (roomtype varchar(4))
as
begin
declare @temprooms table (
rtype varchar(4),
alist varchar(8000) null
)
declare @alist varchar(8000),
@aroom varchar(4)
insert into @temprooms (rtype) select distinct roomtype from dbo.MyTable
while (select count(*) from @temprooms where alist is null) > 0
begin
select top 1 @aroom = rtype from @temprooms where alist is null
select @alist = isnull(@alist,'') + cast(amenity as varchar) + ',' from dbo.MyTable where roomtype = @aroom
select @alist = substring(@alist, 1, len(@alist) - 1)
update @temprooms set
alist = @alist
where
rtype = @aroom
set @alist = null
end
insert into @roomtypes (roomtype)
select rtype from @temprooms where alist = @AmenityList
return
end
go
insert MyTable select 'R001', 1
insert MyTable select 'R001', 2
insert MyTable select 'R001', 3
insert MyTable select 'R002', 1
insert MyTable select 'R002', 2
insert MyTable select 'R002', 4
insert MyTable select 'R003', 1
go
select * from dbo.MyTable
select * from dbo.RoomTypes('1,2,4')
insert MyTable select 'R004', 1
insert MyTable select 'R004', 2
insert MyTable select 'R004', 4
select * from dbo.RoomTypes('1,2,4')
drop function dbo.RoomTypes
drop table dbo.MyTable
-
How about this:
begin
declare @inst varchar(8000),@sumst varchar(8000)
select @inst = '1,2,4'
select @sumst = replace(@inst,',','+')
exec ('select roomtype from mytable
where amenity in ('+@inst+')
group by roomtype
having sum(amenity) = ' + @sumst)
end
You can set it up as a proc to except the comma delimited string. It should work as long as there are no duplicated room/amenity pairs.
-
Lynn, unfortunately your solution seems to take quite a while to run when there are 100,000 + rows.
mrpolecat, your solution runs quite a bit faster ..
kaushik, provided you're using 2005 ... The below solution, runs even faster ..
I will admit to "stealing" Lynn's function idea though.CREATE FUNCTION dbo.getRoomTypes
(@amenitiesList varchar(max))
RETURNS @resultSet TABLE (roomTypes VARCHAR(4))
AS
BEGIN
DECLARE @amenities TABLE
(amenity varchar(4))
DECLARE
@nDex INT
,@aCount INT
SELECT @amenitiesList = replace(@amenitiesList, ' ','')
SELECT @nDex = CHARINDEX(',',@amenitiesList), @aCount = 1
-- build the table to hold the comma delimeted list of typeIDs
WHILE @nDex > 0
BEGIN
INSERT INTO @amenities(amenity)
SELECT CASE WHEN @nDex = 0 THEN
@amenitiesList
ELSE
LEFT(@amenitiesList,@nDex - 1)
END
SELECT @amenitiesList = RIGHT(@amenitiesList,LEN(@amenitiesList) - @nDex)
SELECT @nDex = CHARINDEX(',',@amenitiesList), @aCount = @aCount + 1
END
INSERT @amenities SELECT @amenitiesList;
-- inner join this table to the MyTable
-- and use the rownumber function to get the total matches
WITH roomAmenities
AS
(SELECT
m.roomType
,ROW_NUMBER() OVER(PARTITION BY m.roomtype ORDER BY m.roomtype, m.amenity) AS rn
FROM
dbo.MyTable m
INNER JOIN @amenities a
ON a.amenity = m.amenity)
INSERT @resultSet
SELECT
roomType
FROM
roomAmenities
WHERE
-- we only want rows that have all amenities
rn = @aCount
ORDER BY roomtype
RETURN
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Wooooo! This forum rocks!!
Well thanks for your solution...
Jason --> I was not able to parse your solution as I have SQL Server 2K instead of 2005
tough luck!Mr Polecat --> thanks for this short solution but I feel yours rely on the list being of datatypes on which a SUM() is posssible. So, if I maintain a table with a list like 'EN', 'FR', 'DE', etc then we might need something else. but your solution works great for my above problem!
Lynn --> i implemented your solution and i made a small modification to it...this is because while i said i need columns which matches my list STRICTLY, i think i was too strict with that ;-), so let's say R002 has 1,2,4 and R004 has 1,2,4,5, i would need both because my list is present in both! so i made some changes as you will note below.
THANKS GUYS!!!
/* ADDED */
USE PUBS
SET NOCOUNT ON
/* END */
create table dbo.MyTable (roomtype varchar(4), amenity int)
go
create function dbo.RoomTypes(
@AmenityList varchar(8000) -- varchar(max) -- for SQL Server 2005
)
returns @roomtypes table (roomtype varchar(4))
as
begin
declare @temprooms table (
rtype varchar(4),
alist varchar(8000) null
)
declare @alist varchar(8000),
@aroom varchar(4)
/* ADDED */
declare @templist table
(
amenity varchar(15)
)
-- ** Split @AmenityList string and populate in a table **
declare@string AS varchar(8000)
declare @newstring AS VARCHAR(15)
declare @pos AS INT
declare @len AS INT
SET @string = @AmenityList + ','
SET @len = LEN(@string)
SET @pos = CHARINDEX(',',@string)
WHILE @len != 0
BEGIN
SET @newstring = LTRIM(RTRIM(SUBSTRING(@string,0,@pos)))
IF ISNULL(@newstring,'') ''
BEGIN
INSERT INTO @templist(amenity) VALUES(@newstring)
END
SET @string = SUBSTRING(@string,@pos+1,@len)
SET @len = LEN(@string)
SET @pos = CHARINDEX(',',@string)
END
-- ** END Split @AmenityList string and populate in a table **
/* END ADDED*/
insert into @temprooms (rtype) select distinct roomtype from dbo.MyTable
while (select count(*) from @temprooms where alist is null) > 0
begin
select top 1 @aroom = rtype from @temprooms where alist is null
--select @alist = isnull(@alist,'') + cast(amenity as varchar) + ',' from dbo.MyTable where roomtype = @aroom
/* ADDED */
select @alist =
isnull(@alist,'')
+ CASE WHEN EXISTS (SELECT amenity FROM @templist WHERE amenity = CAST(MT.amenity AS VARCHAR)) THEN cast(MT.amenity as varchar) ELSE '' END
+ ',' from dbo.MyTable MT
where roomtype = @aroom
SELECT @alist = REPLACE(@alist,',,',',')
/* END ADDED*/
select @alist = substring(@alist, 1, len(@alist) - 1)
update @temprooms set
alist = @alist
where
rtype = @aroom
set @alist = null
end
insert into @roomtypes (roomtype)
select rtype from @temprooms where alist = @AmenityList
return
end
go
insert MyTable select 'R001', 1
insert MyTable select 'R001', 2
insert MyTable select 'R001', 3
insert MyTable select 'R002', 1
insert MyTable select 'R002', 2
insert MyTable select 'R002', 4
insert MyTable select 'R003', 1
go
select * from dbo.MyTable
select * from dbo.RoomTypes('1,2,4')
insert MyTable select 'R004', 1
insert MyTable select 'R004', 2
insert MyTable select 'R004', 4
/* ADDED */
insert MyTable select 'R004', 5
/* END */
select * from dbo.RoomTypes('1,2,4')
drop function dbo.RoomTypes
drop table dbo.MyTable
-
Here is a variation that works with other datatypes
begin
declare @inst varchar(8000),@cnt varchar(5),@idx varchar(5)
select @inst = '''EN'',''DE'''
select @cnt = 0
select @idx = 1
while @idx > 0
begin
select @cnt=@cnt+1
select @idx = charindex(',',@inst,@idx+1)
end
exec ('select roomtype from mytable
where amenity in ('+@inst+')
group by roomtype
having count(amenity) = ' + @cnt)
end
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply