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

CodePudding user response：

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.

CodePudding user response：

MySQL INNER JOIN can be used to determine if there's an existing course, and existing staff, and that staff is enrolled in that course:

-- 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 
SELECT CASE WHEN 0 = COUNT(*) THEN -1 ELSE 1 END course_staff_found 
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 = 2
  AND acq_staff.acq_person_id = 1
;

Given course_id = 2 and staffID = 1 outputs:

Given course_id = 5 and staffID = 1 outputs:

...etc., etc., etc.

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

More on JOINS: https://www.tutsmake.com/mysql-inner-left-right-joins-for-beginners/