How do I compare 2 tables looking for missing items.

Question

I have two tables one that contains a huge list of items and another that trading for those items.

Here are examples tables:

The main table

| ID | TITLE | STATUS | TRADE |
-------------------------------
|  1 | test1 |    1   |   1   |
|  2 | test2 |    1   |   1   |
|  3 | test3 |    1   |   0   |
|  4 | test4 |    0   |   1   |

The trade table

| ID | TRADER | ITEM | URL                           |
------------------------------------------------------
|  1 |    2   |   1  | HTTP://www.test.com/itemOne   |
|  2 |    5   |   3  | HTTP://www.test.com/itemThree |
|  3 |    5   |   4  | HTTP://www.test.com/itemFour  |

Say I want to have a list of all the items that are not being traded by trader 5 and have a status of 1. So when trader 5 comes to the site they will be able to select the remaining items to trade.

Here is what I have tried:

$sql = "SELECT m.id, m.title
        FROM main AS m, trade AS t 
        WHERE m.trade >= 1 && m.status = 1 && 
        t.trader <>". mysql_real_escape_string($traderID);

This code just doesn't work. Any ideas on this?

Answer 1

It is not clear to me what column in Trades is an FK to Main. Below, I have assumed it is the Item column:

select m.id, m.title 
from Main m 
where not exists (
    select * 
    from trade 
    where m.id = item 
        and trader = 5
)
    and m.status = 1

Answer 2

Try this:

SELECT id, title FROM main 
WHERE status = 1 AND id NOT IN 
      (SELECT item FROM trade WHERE trader = 5);

This will grab a list of every title in main with a status of 1, but limit the items based on a subquery which gets a list of ids already traded by trader 5 (i.e. items "not in" the list of items returned as having been traded by trader 5).

I'll leave it to you to update the query to be parameterized as needed.

Note that I'm assuming that item in trade is a foreign key to the id field in main, since you didn't specify it.