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

Question

The foreign key trade.ticker_id point to ticker.id.

I want to find all rows in 'ticker' that have zero rows in 'trades' linked to it.

mysql> describe trade; 
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| 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 | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| 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)

I guess it would not be float if this was a bank, but for my application it is the best. I'm using Matlab for analyzing share prices. — ni9e, Sep 26 '15 at 09:04

score 0 · Answer 1 · answered Sep 26 '15 at 08:59

0

You can use left join and is null or in other words anti join

select tc.* from ticker tc
left join trade tr on tr.ticker_id = tc.id
where tr.ticker_id is null

answered Sep 26 '15 at 08:59

Abhik Chakraborty

44,654
6
52
63

Brilliant! Thanks, I'm a total SQL noob. – ni9e Sep 26 '15 at 09:01

score 0 · Accepted Answer · answered Sep 26 '15 at 09:03

0

Try this:

SELECT * FROM ticker WHERE id NOT IN (SELECT ticker_id FROM trade)

answered Sep 26 '15 at 09:03

BYTE RIDER

169
2
12

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

2 Answers2