All we need is an easy explanation of the problem, so here it is.
I have 2 tables, and the CASE statement below should only evaluate to 1/true if the record with the same lead_id exists in both tables. Here I have 2 ways of achieving this but just want to verify if its in fact the most optimal way of achieving this and which one is the better option here? The lead_id in t1 is the primary key and will always exist. I need to verify if its moved into the 2nd table
SELECT CASE WHEN t1.lead_id = t2.lead_id THEN 1 ELSE 0 END AS valid FROM table1 t1 LEFT JOIN table2 t2 ON t1.lead_id = t2.lead_id
SELECT CASE WHEN t2.lead_id IS NOT NULL AND t2.lead_id != 0 THEN 1 ELSE 0 END AS valid FROM table1 t1 LEFT JOIN table2 t2 ON t1.lead_id = t2.lead_id
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can try something like this:
SELECT t2.lead_id IS NOT NULL AS '1' FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.lead_id = t2.lead_id ;
Tested on my server:
SELECT vs.status IS NOT NULL AS '1' FROM vicidial_log vl LEFT JOIN vicidial_statuses vs ON vl.status = vs.status WHERE campaign_id = 'my_campaign'
1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 1 1 1 1 0 1 1 1 1 1 0 0
SELECT t2.lead_id IS NOT NULL FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.lead_id = t2.lead_id ;
A boolean expression evaluates as 0 (false) or 1 (true).
If really want the ids that match instead of 0/1, even simpler:
SELECT t1.lead_id FROM table1 AS t1 JOIN table2 AS t2 ON t1.lead_id = t2.lead_id ;
In all cases,
table2 needs an index starting with
lead_id. (Remember that the
PRIMARY KEY is an index.
If you just want to have an extra field where you see if the entries from t1 are in t2, that is the easiest way. Personally I would have done it with an if statement like so:
SELECT t1.id, IF(t2.id IS NULL,0,1) FROM t1 LEFT JOIN t2 ON t1.id=t2.id;
If you just want to make sure a specific value is in a certain table, you can use the EXISTS() function like so:
SELECT EXISTS(SELECT * FROM t2 WHERE id=<value>);
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂