CODEWITHSUNDEEP
mysqlselect
user1563232
user1563232

Reputation: 361

MySQL - Selecting rows where fields not equal

I have a little problem with an SQL query: I have 'TableA' with a field 'TableA.b' that contains an ID for 'TableB'. I want to select all rows from 'TableB' that don't have an ID that equals any field 'TableA.b'. With other words, I need every row from TableB that's not referred to by any row from TableA in field . I tried a Query like this :

SELECT DISTINCT TableB.* FROM TableA, TableB Where TableA.b != TableB.ID

But the result contains a row that is also returned by the negation, i.e. where both fields have the same value. Any ideas?

Upvotes: 0

Views: 2573

Answers (5)

Abhishek Panjabi
Abhishek Panjabi

Reputation: 439

You can use right join also. Try this:

SELECT DISTINCT TableB.* FROM  tablea RIGHT JOIN TableB ON TableA.b = Tableb.ID WHERE TableA.B IS NULL

Upvotes: 0

Vijay
Vijay

Reputation: 8451

Use NOT IN in SELECT Query.

SELECT * FROM TableB t1 WHERE t1.ID NOT IN (SELECT t2.b FROM TableA t2);

Upvotes: 0

Rick
Rick

Reputation: 443

First, select all ids from TableA:

SELECT DISTINCT b FROM TableA

Then use that result to select all rows in TableB that have an id that does not exist in this set by using the above query as a subquery:

SELECT * FROM TableB WHERE ID NOT IN (SELECT DISTINCT b FROM TableA)

Hope this helps.

Upvotes: 1

e4c5
e4c5

Reputation: 53774

What you need is LEFT (or RIGHT) JOIN.

SELECT TableB.* FROM TableA 
  LEFT JOIN TableB on TableA.b = TableB.ID
    WHERE TableA.b IS NULL

While it's possible to do the same with a subquery as in some of the otehr answers. A join will often be faster.

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things. Today, MySQL Server and many other modern database systems offer a wide range of outer join types.

Upvotes: 1

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

You can try this

  SELECT TableB.* FROM TableB
  WHERE ID NOT IN
       (SELECT b from TableA);

Upvotes: 0

Related Questions