CODEWITHSUNDEEP
phpmysqlsqlselect
Sms
Sms

Reputation: 139

querying for rows in a table and exempting specific rows if they exist in another

I have two different tables with a similar column in both. And i need to query for all rows in table A but must exempt specific rows in table A if those rows exist in table B.

Example:

Table A
---------------------------------
item_id  |  item_name |  price
---------------------------------
  1      |   apple    |   100
---------------------------------
  2      |   banana   |   150
---------------------------------
  3      |   ginger   |   120
---------------------------------
  4      |   pear     |   150
---------------------------------
  5      |   berry    |   120
---------------------------------


Table B
---------------------------------
item_id  |  item_owner | 
---------------------------------
  1      |   Ben       |  
---------------------------------
  2      |   Damian    |  
---------------------------------
  3      |   Greg      |   
---------------------------------

Based on the example above, I need to run a query to fetch all the rows in table A if item_id does not exist in table B.

The result of this query should fetch only 2 rows which are:

---------------------------------
  4      |   pear     |   150
---------------------------------
  5      |   berry    |   120
---------------------------------

Would ber glad to get help with this...Thank!

Upvotes: 0

Views: 63

Answers (4)

hiren soni
hiren soni

Reputation: 174

Try this query...

SELECT A.*
FROM   TableA A FULL OUTER JOIN TableB
       ON A.Item_id = B.Item_ID
WHERE  B.Item_ID IS NULL

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

Select 
A.*
from
A LEFT JOIN B on (A.item_id=B.item_id)
where B.item_id is null

Upvotes: 0

John Woo
John Woo

Reputation: 263723

use LEFT JOIN

SELECT  a.*
FROM    tableA a
        LEFT JOIN tableB b
            ON a.item_id = b.item_id
WHERE b.item_id IS NULL

For faster performance, you must define an INDEX on column item_id on both tables to prevent server to perform FULL TABLE SCAN.

To fully gain knowledge about joins, kindly visit the link below:

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

SELECT *
FROM TableA
WHERE item_id NOT IN(SELECT item_id  FROM tableb);

Demo.

Upvotes: 0

Related Questions