Mysql logic - how to change the selected value based on the data?

Question

I am trying to populate a table with phone number from a temp table. I have wrote the query with no problem. but my peoblem here is to know if the company already has a primary number or not

so I select 2 fields from my temp table called "cvsnumbers" 1) company_code (id) and the phone number.

I need to add a case statement to change the value of a main_number field. so if the number already has a number with main_number = 1 then I need to insert 0 for the new phone number but if there is no main_number then I need to insert 1 for the new phone number making it a primary phone number for the account.

this is my query

    SELECT ac.account_id,
           REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS Phone,
           IFNULL(ta.ext, '') AS extention,
           IFNULL(ta.main_number, 0) AS MainNumber,
           ta.type AS contact_type,
           '2' AS created_by
      FROM cvsnumbers AS ta
INNER JOIN accounts AS ac ON ac.account_id = ta.company_code
     WHERE LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10
       AND REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') NOT IN (SELECT contact_number FROM contact_numbers)

My issue is

`IFNULL(ta.main_number, 0) AS MainNumber,`

I want to change that to some what a case statment to check if a company_code already has a main_number or not.

How can I change this?

Thanks

How would you normally check if ``company_code`` already has a ``main_number``? — mzedeler, May 02 '13 at 18:22
CASE WHEN main_number = 1 THEN 0 ELSE 1 END. But this this case I have to do some sort of sub query to check if a company_code has a number that exists with a main_number = 1 — Mike, May 02 '13 at 18:32
This will list all primary phone numbers in the table. SELECT contact_number FROM contact_numbers WHERE main_number = 1 — Mike, May 02 '13 at 18:37

mzedeler · Accepted Answer · 2013-05-02T19:54:12.453

0

I am still not sure exactly how your query should look like, but how about something along theese lines?

SELECT CASE 
            WHEN EXISTS (SELECT *
                           FROM contact_numbers
                          WHERE main_number = 1
                            AND contact_number = 
                           <insert contact number from outer expression here>) 
            THEN 1
            ELSE 0
        END AS MainNumber
  FROM ...

edited May 02 '13 at 19:54

answered May 02 '13 at 17:59

mzedeler

4,177
4
28
41

Thanks for that. I know how to write a case statement of if statement. but I am not sure how to do it and checking on every record during my select statement – Mike May 02 '13 at 18:16
Thank you:) I will try it and I will let you know – Mike May 02 '13 at 20:20
I have finally got a chance to test it and it worked. thanks you so much sir :) – Mike May 14 '13 at 14:45

Mysql logic - how to change the selected value based on the data?

1 Answers1