Question 113146 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I change SELECT statement to IF statement in Mysql?

In mysql, I tried to print ‘-1’ if 3 conditions are satisfied.

SELECT '-1' 
WHERE not exists(select * from acq_staff where acq_person_id = staffID) 
OR    not exists(select * from acq_training_course_session where training_course_id = course_id) 
OR    exists(select * from acq_training_enrolment where acq_staff_acq_person_id = staffID);

But how can I change this SELECT statement to IF statement so that if either those 3 conditions are satisfied, print -1 otherwise I am going to insert a data.

Sorry for not enough information

Tags:

4

Answers


  1. 0

    I guess you can do something like this: How can I simulate a print statement in MySQL?

    `mysql>SELECT 'some text' as '';
+-----------+
|           |
+-----------+
| some text |
+-----------+
1 row in set (0.00 sec)`

    and just instead of some text set -1.

    And one more thing i noticed in your question, that part "if those 3 conditions are satisfied" if you want all 3 conditions to be satisfied you need to change OR to AND. Because in your case, with OR, there needs to be satisfied only 1 condition, but with AND all 3 of them need to be satisfied.

    Login or Signup to reply.
  2. 0

    MySQL INNER JOIN, along with WHERE NOT EXISTS, can be used to determine if there’s an existing course, and existing staff, and that staff is enrolled in that course, and if not, INSERT the staff and course id in the enrollment table.

    -- create
CREATE TABLE acq_staff (
  acq_person_id INTEGER PRIMARY KEY
);
CREATE TABLE acq_training_course_session (
  training_course_id INTEGER PRIMARY KEY
);
CREATE TABLE acq_training_enrolment (
  training_course_id INTEGER NOT NULL,
  acq_staff_acq_person_id INTEGER NOT NULL
);

-- insert
INSERT INTO acq_staff VALUES (1), (2), (3);
INSERT INTO acq_training_course_session VALUES (1), (2), (3), (4);
INSERT INTO acq_training_enrolment VALUES (1,1), (1,2), (2,1), (3,1);

-- fetch 
INSERT INTO acq_training_enrolment (training_course_id, acq_staff_acq_person_id) 
SELECT 3, 1 WHERE NOT EXISTS
(SELECT * 
FROM acq_training_course_session
  INNER JOIN acq_training_enrolment 
    ON acq_training_course_session.training_course_id = acq_training_enrolment.training_course_id
  INNER JOIN acq_staff ON acq_training_enrolment.acq_staff_acq_person_id = acq_staff.acq_person_id
WHERE acq_training_course_session.training_course_id = 3
  AND acq_staff.acq_person_id = 1)
;

    Try it here: https://onecompiler.com/mysql/3yk7xynkg

    Login or Signup to reply.
  3. 0

    maybe you can try that

    select 
if(
    acq_staff.acq_person_id = staffID , '-1' , 
    if(
        acq_training_course_session.training_course_id = course_id , '-1' , 
        if(acq_training_enrolment.acq_staff_acq_person_id = staffID , '-1' , 'not exist')
    ) 
) as "check" from acq_staff , acq_training_course_session , acq_training_enrolment limit 1
    Login or Signup to reply.
  4. 0

    The question is about how to conditionally execute an insert query. In pseudo-code, the question asks how to do the following

    flag = (SELECT ... WHERE <all the conditions are met>)
IF flag == 1
   INSERT INTO ....
ELSE IF flag == -1
   DO NOTHING

    Now think about it this way

    result_set = (SELECT ... WHERE <all the conditions are met>)
# result_set here is the actual rows we want to insert
# length(result_set) > 0 if conditions are met
# length(result_set) == 0 if conditions are not met

INSERT INTO ... (result_set)

    or simply

    INSERT INTO ... (SELECT ... WHERE <all the conditions are met>)

    When <all the conditions are met>, the insert will actually have something to insert. Otherwise, it will have an empty result set so no rows will be inserted.

    So use INSERT INTO ... SELECT ... WHERE <all the conditions are met> Syntax to achieve desired results. Unfortunately, this solution does not have a way to return back -1.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search