1

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

Query 1

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

Query 2

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
1
  • Thanks @ErgestBasha for the suggestion but I need a 0 or 1 generated instead and not the actual lead_id Commented Apr 28, 2022 at 13:44

3 Answers 3

2

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'

Result:

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
1

even shorter...

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.

0

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>);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.