AND operator in SELECT statement

Question

Post reply

AND operator in SELECT statement

hr_sn

Ten Centuries

Points: 1122
More actions
November 20, 2006 at 4:28 am

#368073

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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)

&nbsp dtSplitted WHERE LEN(dtSplitted.EachID) > 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

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

hr_sn Ten Centuries Points: 1122 More actions · Answer 3

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.

hr_sn Ten Centuries Points: 1122 More actions · Answer 4

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

)

AND operator in SELECT statement

Cookies on SQLServerCentral