Compare 2 result sets without INTERSECT

Question

I have 2 tables: -

ITEM(ITEM_ID, ITEM_NAME)

STATS(ITEM_ID, STAT_ID, STAT_VALUE)

I would like to return ITEM_IDswith exactly the same STATS, but it is on SQL Server Compact (no EXCEPTor INTERSECT)

For instance:

STATS:-
1 12 100 
1 13 500
2 12 200
2 14 300
3 12 100
3 13 500
4 12 100

Must return rows for 1 and 3 (same values ​​/ values ​​12/100 and 13/500)

Is this possible without INTERSECT?

Answer 1

use the inner join in the ITEM table and use provided that you want to see in this table

Answer 2

For the first time I was wrong :-)

" " , , ( CROSS JOINs).

AFAIK SQL Server CE XML, , :

with cte as
 ( -- group concat all rows for one ITEM_ID into one big string
   SELECT distinct ITEM_ID,
     (select '#' + rtrim(STAT_ID) + ',' + rtrim(STAT_VALUE) 
      from STATS as t2 
      where t1.ITEM_ID = t2.ITEM_ID ORDER BY STAT_ID FOR XML PATH('') ) as rowsConcat
   FROM STATS as t1
 ),
cnts as
 ( -- how many rows exist for that concatenated string?
   select *
     ,count(*) 
      over (partition by rowsConcat) as cnt
   from cte
 ) 
select ITEM_ID
  ,dense_rank() -- assign the same group number to the duplicates 
   over(order by rowsConcat) as duplicateGroup
from cnts
where cnt > 1 -- more than one

fiddle

Answer 3

.

1. / . STAT_ID STAT_VALUE. . , :

STAT_ID STAT_VALUE
12      100 

:

single_value
12_100

:

ITEM_ID single_value
1       12_100 
1       13_500
2       12_200
2       14_300
3       12_100
3       13_500
4       12_100

2.. , . CLR, T-SQL FOR XML. , SQL Server CE.

:

ITEM_ID aggregated_single_value
1       12_100__13_500
2       12_200__14_300
3       12_100__13_500
4       12_100

3.. , GROUP BY aggregated_single_value COUNT ITEM_IDs , . ITEM_IDs, HAVING 1.

aggregated_single_value  Count
12_100__13_500           2
12_200__14_300           1
12_100                   1

Edit

- SQL Server CE.

• Common-Table-Expressions - .
• FOR XML, , CLR - "". , . - , .
• , varbinary(max) varchar(max)? - , - varchar(8000) varbinary(8000). STATS ( ) ITEM_ID, 8000 .

, , , , . ITEM_ID STATS IDs, . , . ID=4 .

Answer 4

, SQL Server Compact , :

select distinct a.item_id 
       from  stats a 
       where exists(select 1 
                           from stats b 
                           where a.stat_d=b.stat_id 
                            and a.stat_value=b.stat_value)

Answer 5

INTERSECT
, , INTERSECT.

INTERSECT JOIN :

SELECT A.*
FROM A   -- [A: ID, Name]
INTERSECT
SELECT B.*
FROM B  -- [B: ID, Name]

SELECT DISTINCT A.*
FROM A
JOIN (
    SELECT B.*
    FROM B) B1 ON A.ID = B1.ID AND A.Name = B1.Name

EXISTS:

SELECT DISTINCT A.*
FROM A
WHERE EXISTS (
    SELECT 1 
    FROM B WHERE A.ID = B.ID AND A.Name = B.Name)

---

Try this query:

SELECT DISTINCT s1.STAT_ID, s1.STAT_VALUE
FROM STATS s1
    JOIN
    STATS s2 ON s1.ITEM_ID <> s2.ITEM_ID 
            AND s1.STAT_ID = s2.STAT_ID 
            AND s1.STAT_VALUE = s2.STAT_VALUE