SQL Query Duplicating records

0
votes

I've got two tables. Let's call them table_A and table_B.

Table_B contains the ForeignKey of table_A.

Table_A

ID         Name
1          A
2          B
3          C

Table_B



ID         table_a_fk
1          2 
2          3

Now I want to get all the names out of table_a IF table_b does not contain the ID of the record in table_a.

I've tried it with this query:

SELECT a.name
FROM table_a a, table_b b
WHERE a.id != b.table_a_fk

With this Query I'm getting the right result I just get this result like 5times and I don't know why.

Hope someone can explain me that.

2
sqloracleplsql

2 Answers

1
votes

Your query creates a cartesian product between your two tables A and B. It is the cartesian product that generates those duplicate values. Instead, you want to use an anti-join, which is most commonly written in SQL using NOT EXISTS

SELECT a.name
FROM table_a a
WHERE NOT EXISTS (
  SELECT *
  FROM table_b b
  WHERE a.id = b.table_a_fk
)

Another way to express an anti-join with NOT IN (only if table_b.table_a_fk is NOT NULL):

SELECT a.name
FROM table_a a
WHERE a.id NOT IN (
  SELECT b.table_a_fk
  FROM table_b b
)

Another, less common way to express an anti-join:

SELECT a.name
FROM table_a a
LEFT OUTER JOIN table_b b ON a.id = b.table_a_fk
WHERE b.id IS NULL
1
votes

use distinct

SELECT distinct  a.name
FROM table_a a, table_b b
WHERE a.id != b.table_a_fk

or better is...

Select distinct name
from tableA a
Where not exists (Select * from tableB 
                  Where table_a_fk = a.id)