CODEWITHSUNDEEP
sqlt-sql
Ender Kaya
Ender Kaya

Reputation: 138

Using Intersect where some values in SQL

I have a table likes this;

 PROPERTY1  |  PROPERTY2 
 -----------+-------------
  A         | 1
  A         | 2
  A         | 3
  B         | 2
  B         | 4
  B         | 6
  C         | 5

I just want to see the intersection of results.

Example; I want to see "2" result as the intersection of A and B. But if I use A,B and C for conditions, query must returns empty.

How do i this basically?

This two solutions are working but if I use too much property1 values (in where command), i get huge queries. is there a simple solution?

SELECT PROPERTY2
FROM TABLE1 AS X WHERE X.PROPERTY1 IN ('A')
INTERSECT
SELECT PROPERTY2
FROM TABLE1 AS T WHERE T.PROPERTY1 IN ('B')

And 

SELECT DISTINCT PROPERTY2 FROM TABLE1 WITH (NOLOCK) 
WHERE 
PROPERTY1=N'A' 
AND OZELLIK2 IN (SELECT DISTINCT PROPERTY2 FROM TABLE1 WITH (NOLOCK) WHERE PROPERTY1 IN ('B') )
ORDER BY PROPERTY2 ASC

Upvotes: 0

Views: 402

Answers (3)

James Casey
James Casey

Reputation: 2507

This might be a way - pass a table valued param in and join it to the data - where the count of results found for a given property is the same number as the count of values passed in, you have an intersect. It works for this noddy example, I'm not 100% sure it cant be tripped up.

If you can't use TVPs for whatever reason, you could pass in a csv string and split it up.

create table Prop
(
    property1 char(1),
    property2 int
)

insert into Prop values('A',1),('A',2),('A',3),('B',2),('B',4),('B',6),('C',5)

create type Property1TableType as table (property1 char(1))
go

create procedure usp_GetIntersect
    @params Property1TableType readonly
as

-- count the number of distinct params
declare @c int
select @c = count(distinct property1) from @params

-- find where the number of distinct values after joining is the same as the number of distinct params
select distinct property2
from Prop
inner join @params tt on Prop.property1 = tt.property1
group by property2
having count(distinct tt.property1) = @c

go

-- run our procedure
-- first create the parameter table to pass
declare @p Property1TableType
insert into @p values('A'),('B')

exec usp_GetIntersect @p

-- now also insert C and run again
insert into @p values('C')

exec usp_GetIntersect @p

Upvotes: 1

Erwin Smout
Erwin Smout

Reputation: 18408

Based on your comment "Example; I want to see "2" result as the intersection of A and B. But if I use A,B and C for conditions, query must returns empty.", it looks like the operation you want/need is that of relational division.

There are only indirect ways of writing the operation in SQL (and there is a multitude of them without much of any objective criterion to decide which is "better") but the following phases of computation are usually involved :

(1) determine your set of PROPERTY1 values ( {A,B} or {A,B,C} )
(2) determine the "candidate" PROPERTY2 values ( SELECT PROPERTY2 FROM your_tbl JOIN tbl_from_1 ON ... .PROPERTY1 = ... .PROPERTY1
(3) construct the "maximal" table by joining (1) and (2)
(4) subtract your_tbl from (3), this gives you all the PROP1/PROP2 combos that do not appear in your_tbl.
(5) SELECT PROPERTY2 FROM (4) , this gives you all the PROPERTY2 values that do not appear in your-tbl for all PROPERTY1 values involved in (1)
(6) subtract (5) from (2), this gives you all the PROPERTY2 values that do appear in your_tbl for all PROPERTY1 values involved in (1).

The full story on relational division is way too long to fit in this space here, if you want to learn more then Google is your friend.

Upvotes: 1

pm_li
pm_li

Reputation: 43

Try sth. like "self-join"

 CREATE TABLE #TMP( PROP_1 VARCHAR(1), PROP_2 INT)

INSERT INTO #TMP 
SELECT 'A', 1
INSERT INTO #TMP 
SELECT 'A', 2
INSERT INTO #TMP 
SELECT 'A', 3
INSERT INTO #TMP 
SELECT 'B', 2
INSERT INTO #TMP 
SELECT 'B', 4
INSERT INTO #TMP 
SELECT 'C', 6


DECLARE @I VARCHAR(5)
DECLARE @SQL  NVARCHAR(MAX) = ''
declare curs SCROLL cursor for 
select distinct prop_1 from #TMP where prop_1 in ('A','B','C') 
OPEN curs;
FETCH NEXT FROM curs INTO @I;
WHILE @@FETCH_STATUS=0
    BEGIN
          SET @SQL += ' SELECT PROP_2 FROM #TMP WHERE PROP_1 = ''' + @I + '''' ;    

    FETCH NEXT FROM curs INTO @I;

    if @@FETCH_STATUS=0
    begin
        set @SQL +=  ' INTERSECT';
    end

    END
CLOSE curs   
DEALLOCATE curs

PRINT @SQL

exec sp_executesql @SQL

DROP TABLE #TMP

Upvotes: 1

Related Questions