Select Exclusivly IN
-
Appologies if this has been covered already, i lack the terminology for what i'm trying to achive.
In a given table
fldPK int fldModelID int fldColID int Where the PK is unique.
I want to return all fldModID's where fldColID is IN(1,3,16,27). But rather than Any of (1,3,16,27), i want a statment that implements a "Must Contain All of (1,3,16,27)"
if fldModel relates to a Model, and fldColID is Colour. Doing a search on models, that are avaiable in ALL of the following (1,3,16,27)
Hope that makes sense
Thanks in advanced
-
If I understand this correctly WHERE fldModelID IN (1, 2, 3) will return items you don't want because they can have 1 but not 3.
What you need to do is WHERE fldModelID = 1 AND fldModelID = 2 AND fldModelID = 3....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
What you need to do is WHERE fldModelID = 1 AND fldModelID = 2 AND fldModelID = 3....
Assuming that column only contains a scalar integer value, how should this ever work?
Maybe sample data and required output can bring some light into this.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here's a template... I'll be waiting for your questions
.IF Object_id('Split') > 0
DROP FUNCTION SPLIT
GO
CREATE FUNCTION [dbo].[Split] (@vcDelimitedString nVarChar(4000),
@vcDelimiternVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR:Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1), --Array index
ElementnVarChar(1200)--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
GO
IF NOT Object_id('SearchColumns') IS NULL
DROP PROCEDURE SearchColumns
GO
CREATE PROCEDURE dbo.SearchColumns @Items as varchar(8000), @ItemsCount as smallint --avoid calling split twice
AS
SET NOCOUNT ON
SELECT
O.id
, O.Name
FROMdbo.SysObjects O
WHERE Exists(
SELECT 1
FROMdbo.SysColumns C
WHERE C.id = O.id and C.Name in (Select Element from dbo.Split(@Items, ','))
GROUP BY id having count(*) >= @ItemsCount
)
ORDER BYName
SET NOCOUNT OFF
GO
--find all items
Exec dbo.SearchColumns 'name,id', 2-- 4 row(s) affected
Exec dbo.SearchColumns 'name,id,indid', 3-- 1 row(s) affected
Exec dbo.SearchColumns 'id', 1-- 12 row(s) affected
--find at least 2
Exec dbo.SearchColumns 'name,id,indid', 2-- 5 row(s) affected (instead of 1)
--find at least 1
Exec dbo.SearchColumns 'name', 1-- 10 row(s) affected
Exec dbo.SearchColumns 'name,indid', 1-- 11 row(s) affected
GO
DROP PROCEDURE SearchColumns
--DROP FUNCTION Split
GO
-
I'll be waiting for your questions
Have you read the original question and noticed the given table structure
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes I did and I provided an exemple... better that way so he'll have to understand the code to use it
.As promised to myself yesterday, I'll not post the un-set version of the split function, so here's the new script :
IF Object_id('fnSplit_Set') > 0
DROP FUNCTION fnSplit_Set
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter)) - PkNumber - len(@vcDelimiter)) as EachID
FROM dbo.Numbers
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
IF Object_id('Numbers') IS NULL
BEGIN
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
Declare @i as int
set @i = 0
while @i = @ItemsCount
)
ORDER BYName
SET NOCOUNT OFF
GO
--find all items
Exec dbo.SearchColumns 'name,id', 2-- 4 row(s) affected
Exec dbo.SearchColumns 'name,id,indid', 3-- 1 row(s) affected
Exec dbo.SearchColumns 'id', 1-- 12 row(s) affected
--find at least 2
Exec dbo.SearchColumns 'name,id,indid', 2-- 5 row(s) affected (instead of 1)
--find at least 1
Exec dbo.SearchColumns 'name', 1-- 10 row(s) affected
Exec dbo.SearchColumns 'name,indid', 1-- 11 row(s) affected
GO
DROP PROCEDURE SearchColumns
--DROP FUNCTION Split
GO
-
Putting it all together. This is how I understand this problem.
create table #showme
(
c1 int
, c2 int
)
insert into #showme
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 2,1
union all
select 2,3
declare @comma varchar(1000)
set @comma = '1,2,3'
declare @table table(p2 int)
insert into @table
select
right(left(@comma,Number-1)
, charindex(',',reverse(left(','+@comma,Number-1))))
from
master..spt_values -- what a suprise here !!!
where
Type = 'P' and Number between 1 AND len(@Comma)+1
and
(substring(@Comma,Number,1) = ',' or substring(@Comma,Number,1) = '')
select c1
from #showme
where c2 in(select p2 from @table)
group by c1
having count(distinct c2)=(select count(*) from @table)
drop table #showme
c1
-----------
1
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You undertand it just the way I do. The only difference is that your solution is more rigid
. -
Thank you so much, for the solutions. Just trying to get my head round them first then implement them.
-
If I understand this correctly, you're looking for all the rows where the model is available in each listed color.
Maybe something like this:
select * from myTable a
where exists
(
select 1 from myTable b
where b.fldModelId = a.fldModelId
and b.fldColID = 1
)
and exists
(
select 1 from myTable c
where c.fldModelId = a.fldModelId
and c.fldColID = 3
)
and exists
(
select 1 from myTable d
where d.fldModelId = a.fldModelId
and d.fldColID = 16
)
and exists
(
select 1 from myTable e
where e.fldModelId = a.fldModelId
and e.fldColID = 27
)
I know it's not a general solution, but maybe it can get you going.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply