mysql - Find all rows from table1 that have zero rows in table2 linked to it by foreign key -

this question has answer here:

• return row if value doesn't exist 2 answers

the foreign key trade.ticker_id point ticker.id.

i want find rows in 'ticker' have 0 rows in 'trades' linked it.

mysql> describe trade;  +-----------+-------------+------+-----+---------+----------------+ | field     | type        | null | key | default |          | +-----------+-------------+------+-----+---------+----------------+ | id        | int(11)     | no   | pri | null    | auto_increment | | time      | datetime    | no   |     | null    |                | | price     | float       | no   |     | null    |                | | quantity  | int(11)     | no   |     | null    |                | | source    | varchar(64) | yes  |     | null    |                | | buyer     | varchar(64) | yes  |     | null    |                | | seller    | varchar(64) | yes  |     | null    |                | | initiator | varchar(64) | yes  |     | null    |                | | ticker_id | int(11)     | yes  | mul | null    |                | +-----------+-------------+------+-----+---------+----------------+ 9 rows in set (0,00 sec)  mysql> describe ticker; +-------------+--------------+------+-----+---------+----------------+ | field       | type         | null | key | default |          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      | no   | pri | null    | auto_increment | | name        | varchar(64)  | no   |     | null    |                | | long_name   | varchar(250) | yes  |     | null    |                | | exchange_id | int(11)      | yes  | mul | null    |                | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0,00 sec) 

try this:

select * ticker id not in (select ticker_id trade)