Should be an easy Select, shouldn''t it?
-
Sorry if this is an easy one, but it just has me stumped.
I have a table with 2 columns, here's a quick snapshot of the data:
applicantid skillid
387 1
387 2
387 5
387 7
388 1
388 2
388 3
388 5
388 7
I need to write a Select statement that selects all applicant ids for, say, skillids 1 and 3. But they must have them BOTH.
Can't use IN and OR, as this selects people who have at least one of them.
Any pointers would be appreciated.
Cheers
pie
-
try this
select applicantid from aa
where skillid = 1 and applicantid = (select applicantid from aa where skillid = 3)
-
Without using in and making sure it can't fail :
select applicantid from aa A
where skillid = 1 and exists (select * from aa B where skillid = 3 and A.applicantid = B.applicantid)
-
Or for a incredibly more powerfull solution :
IF EXISTS (Select * from dbo.SysObjects where name = 'fnSplit_Set' and XType IN ('FN', 'IF', 'TF'))
DROP FUNCTION dbo.fnSplit_Set
GO
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U')
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
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 EachID from dbo.fnSplit_Set(@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 fnSplit_Set
GO
-
Ummmm .... Could you explain what that all does ??
-
Which part is giving you difficulties?
-
I understand your & Atra's select statements, but I don't understand that big query you posted after that. I ran it, and I got a bunch of system stuff, but I don't get how it's relevant to the original question (or what it means anyway) .... sorry
-
This is the proc that does the job :
CREATE PROCEDURE dbo.SearchColumns @Items as varchar(8000), @ItemsCount as smallint --avoid calling split twice
AS
SET NOCOUNT ON
SELECT
O.id
, O.Name
FROM dbo.SysObjects O
WHERE Exists (
SELECT 1
FROM dbo.SysColumns C
WHERE C.id = O.id and C.Name in (Select EachID from dbo.fnSplit_Set(@Items, ','))
GROUP BY id having count(*) >= @ItemsCount
)
ORDER BY Name
SET NOCOUNT OFF
GO
It uses the system tables as an exemple just to make sure it works on everyone's pc. The exemples at the end show how to use it to get all columns wanted or only a certain number. The other big code is the set based split function... I'll let you figure this one out if you wish
. -
What's a split set ?
I guess the whole post went flying over my head ....
-
Splits '1,2,3,4' to
1
2
3
4
where the comma is the delimiter in this case. I won't go into the mechanics of the code but at least you'll know what it's for. You could also enjoy reading this :
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply