exclude records which match on both tables sql

Question

Its not a duplicate of another question because I have two tables , when inner joined on there t-numbers should exclude the search criteria and I cant use distinct on search criteria.

I have two tables

Table A :

T_ref            t_number
VC04706610      1369473
VC0484351A      1357916 
VC04882679      1311572

Table B :

t_number  thr_hold_type
1369473    2877417
1369473    2877657
1357916    2877417
1357916    2877635
1357916    2877697
1311572    2877637
1311572    2877572

I want to exclude records which have hold type 2877417.

Query :

SELECT t_ref
 FROM table a
 INNER JOIN table b
     ON b.t_number = a.t_number
 WHERE thr_hold_type NOT IN ('2877417') 
     AND t_reference IN ( 'VC04706610', 'VC0484351A',b'VC04882679', 'VC04885203')

When i run this query it brings up all other hold types I want to completely exclude these t_references which has that hold type..

Possible duplicate of [How to select unique records by SQL](http://stackoverflow.com/questions/1641718/how-to-select-unique-records-by-sql) — Pavel V., Feb 10 '16 at 11:36
is "t-reference" in the WHERE supposed to be the same attribute as "t_ref" in the SELECT? — Anton, Feb 10 '16 at 11:59
@KiranV , In your example only VC04882679 should be returned, right? — sagi, Feb 10 '16 at 12:04
@KiranV Then my answer is the answer you need, nothing is wrong with it. — sagi, Feb 10 '16 at 12:27
I edited your attribute names for you as no one else seemed to be interested in doing so :D You should take care in future to have the correct attributes in your query and just remain consistent in general. Otherwise it can be difficult to figure out what exactly the question is, if your problem is one of simply syntactic errors or if there is something else. It may seem like a pedantic detail but it adds a ton of work to answering the question accurately. — Anton, Feb 10 '16 at 13:15

Anton · Answer 1 · 2016-02-10T11:38:38.630

0

Have you tried the simple:

     SELECT *
     FROM  tableA
     JOIN tableB 
     ON tableA.t_number = tableB.t_number
     WHERE thr_hold_type != 287741

edited Feb 10 '16 at 11:38

answered Feb 10 '16 at 11:32

Anton

422
2
9

That is what the question was though..."I want to exclude records which have hold type 2877417". – Anton Feb 10 '16 at 12:33
1

I dont know what answer you want, but this is the answer to the question you asked. – Anton Feb 10 '16 at 12:39

score -1 · Answer 2 · answered Feb 10 '16 at 11:42

-1

Try this:

select t_ref from TableA a
where a.t_number not in
  (select b.t_number from TableB b where b.thr_hold_type = '2877417')

answered Feb 10 '16 at 11:42

Svperstar

487
2
10

score -1 · Answer 3 · answered Feb 10 '16 at 11:45

Hi bellow query should work, there are data duplicates in your data that may be creating problem, also you can use exist clause instead of inner join.

with a as
( select 'VC04706610' t_ref,  1369473 t_number from dual
union
select 'VC0484351A' t_ref,  1357916 t_number from dual
union
select 'VC04882679' t_ref,  1311572 t_number from dual
),
b as
(
select 1369473 t_number,   2877417 thr_hold_type from dual
union
select 1369473 t_number,   2877657 thr_hold_type from dual
union
select 1357916 t_number,   2877417 thr_hold_type from dual
union
select 1311572 t_number,   2877637 thr_hold_type from dual
union
select 1311572 t_number,   2877572 thr_hold_type from dual
)
select * from a inner join b on a.t_number = b.t_number
and thr_hold_type <> 2877417
and t_ref in ('VC04706610',
'VC0484351A',
'VC04885203')

exclude records which match on both tables sql

3 Answers3