AND operator in SELECT statement
-
Hi All,
I'm trying to achieve AND operator using SELECT statement. I understand, we can achieve OR operator using IN clause like,
SELECT * FROM Employee WHERE EmployeeID IN (SELECT ID FROM #temp)
This statement will give me the records from Employee table where all or any of the ID from #temp exists in Employee table. What I basically need to is get the rows from Employee table, if and only if, all IDs from #temp table exist in Employee table.
I also understand, this can be achieved by using dynamic SQL, but I don't want to go there.
I hope I've made my point clear and I'm sure this can be done using T-SQL.
Thanks in advance,
hr_sn
-
Are you looking to find employees not listed in temp?
>> Select * FROM dbo.Employees E where NOT exists (Select * from #temp t where E.empid = t.empid)
Because my first impression of your needs just doesn't apply to this case!?!? (like find all employees who speak all (not any) those languages (french, english, spanish).
If I didn't answered you question, can you post some sample data and the required output from that data?
-
The more versatile solution to the second variation of the problem goes like this. (can be simplified but I don't have time for it now). Also I expect you'll have questions about this code so don't be afraid to ask
.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 < LEN(@vcDelimiter + @IDs + @vcDelimiter)
 
dtSplitted WHERE LEN(dtSplitted.EachID) > 0GO
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
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
--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
-
Thanks for your replies Ninja, but my situation is little bit different.
sample data:
declare @employee table (EmployeeId int, EmpName varchar(32))
insert into @employee values (1, 'test1')
insert into @employee values (2, 'test2')
insert into @employee values (3, 'test3')
insert into @employee values (4, 'test4')
==> Case 1
declare @temp table (id int)
insert into @temp values (1)
insert into @temp values (4)
Give me all data from employees if IDS data in @temp exist in Employee table. Now, since ID 1 and 4 exist in Employee table, it should give me all rows from employee table not just rows for ID (1,4)
==> case 2
insert into @temp values (1)
insert into @temp values (6)
insert into @temp values (7)
Same condition, even if ID 1 exist in employee table but not the ID 6 and 7, so I shouldn't get any rows from employee table.
-
Never mind, I found the solution:
;with c AS
(
select * from @employee
)
select *
from c
where not exists
(
select id from @temp
except
select Employeeid from c
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply