CODEWITHSUNDEEP
sqloracle-databaseintersect
sabisabi
sabisabi

Reputation: 1511

How to use intersect (and maybe minus) on this example

lets say I have two tables:

TableA
ID,    Time,   Size

0      5:00    600
1      8:00    800
2      7:00    100
3      1:50    140
4      2:40    300 
12     3:40    300

TableB
ID,    Time,   Size

8      5:00    600
1      8:00    800
2      8:00    900
3      1:50    140
5      2:40    300 
12     3:40    300

Now I would like to compare these two Tables: give all rows from Table A and B where the IDs are same (or lets say where the IDs are existing in both of the Tables) but lower then 12 - so that would kick out table A ID 0, 4, 12 and from table B ID 8, 5, 12

After that I would have only 3 rows left. Now I would like to kick out all rows where there is one or more difference between the whole TableA row ID == TableB row ID

At the end there would be (if I see that correct) as output:

ID,    Time,   Size
1      8:00    800
3      1:50    140

For this solution I need for sure intersect and maybe minus. At first I tought this SQL statement would do what I want:

select * from TableA where ID < 12 intersect select * from TableB where ID < 12 
minus 
select * from TableB where ID < 12;

But this is not working that well. Ist because of the intersect, Im using intersect for the whole row, I should use intersect for only the IDs and IF I have the intersect of the IDs (that would be 1,2,3) then I have to use ID TableA with ID 1,2,3 minus TableB with ID 1,2,3. But how? Or do I miss something? Any ideas? Thank you

Upvotes: 0

Views: 13288

Answers (2)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

select id from TableA where ID < 12 and id in (Select id from TableB)
union 
select id from TableB where ID < 12   and id in (Select id from TableA)

or

SELECT id, 
       time, 
       size 
FROM   (SELECT * 
        FROM   tablea 
        WHERE  id < 12 
        UNION ALL
        SELECT * 
        FROM   tableb 
        WHERE  id < 12) a 
GROUP  BY id, 
          time, 
          size 
HAVING Count(*) = 2

or

SELECT * 
FROM   tablea A 
WHERE  EXISTS (SELECT 1 
               FROM   tableb b 
               WHERE  b.id = a.id 
                      AND b.time = a.time 
                      AND b.size = a.size)

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

From your requirements and the testdata you've provided, you don't need intersect at all. A mere INNER JOIN would suffice.

SELECT a.* 
FROM   TableA a 
       INNER JOIN TableB b ON b.ID = a.ID AND b.Time = a.Time AND b.Size = a.Size

Upvotes: 3

Related Questions