Find missing ids between mysql tables

Question

When I run the following mysql statement, I get 127 rows returned:

SELECT t.strat_id, t.candle_id, t.spot_entry, t.spot_close, t.f,t.fa,t.b,t.we,t.direct,t.date_created,t.date_closed,t.notes,t.results FROM cp_user_trades AS t INNER JOIN candles2 AS c ON t.candle_id = c.id WHERE t.user_login = "user" AND t.active=0 AND t.deleted=0 ORDER BY t.date_closed DESC

When I remove the INNER JOIN I get 131 rows returned:

SELECT t.strat_id, t.candle_id, t.spot_entry, t.spot_close, t.f,t.fa,t.b,t.we,t.direct,t.date_created,t.date_closed,t.notes,t.results FROM cp_user_trades AS t WHERE t.user_login = "user" AND t.active=0 AND t.deleted=0 ORDER BY t.date_closed DESC

How can I SELECT the 4 rows that are not being returned in the first statement?

Thank you!

David Stetler · Accepted Answer · 2014-07-02 21:52:32Z

2

assuming c.ID is the primary key for the candles2 table:

Use a Left Join and add where c.Id is null

answered Jul 2, 2014 at 21:52

David Stetler

1,46110 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-07-02 21:59:44Z

To find ids between to tables, I use union all and group by:

select in_cut, in_c2, count(*) as cnt, min(candle_id), max(candle_id) from (select candle_id, sum(in_cut) as in_cut, sum(in_c2) as in_c2 from ((select candle_id, 1 as in_cut, 0 as in_c2 from cp_user_trades ) union all (select id, 0, 1 from candles2 ) ) cc group by candle_id ) c group by in_cut, in_c2;

This gives you the three possibilities of ids in the two tables (in the first, in the second, and in both). It shows whether ids are duplicated in either table, and it gives examples of the ids.

J-Dizzle · Accepted Answer · 2014-07-02 22:18:39Z

My suggestion is to use the second query that returns 131 rows, with a subquery that makes it not include the 127 that are found by the other.

SELECT t.strat_id, t.candle_id, t.spot_entry, t.spot_close, t.f,t.fa,t.b,t.we,t.direct,t.date_created,t.date_closed,t.notes,t.results FROM cp_user_trades AS t WHERE t.user_login = "user" AND t.strat_id NOT IN (SELECT t.strat_id FROM cp_user_trades AS t INNER JOIN candles2 AS c ON t.candle_id = c.id WHERE t.user_login = "user" AND t.active=0 AND t.deleted=0 ORDER BY t.date_closed DESC) AND t.active=0 AND t.deleted=0 ORDER BY t.date_closed DESC

I thought t.strat_id would be the one to exclude. I dont know what the primary(unique) key is, but the line AND t.strat_id NOT IN (SELECT t.strat_id you can replace with t.candle_id if that's what's important. There are other types of joins though, which are definitely more efficient than this.

Collectives™ on Stack Overflow

Find missing ids between mysql tables

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related