Nrthugu

What I'm trying to achieve is to insert values into a table if that value does not exist in 2 extra tables.

INSERT INTO visitor(

  visitor_username,

  email,

  PASSWORD

)

SELECT * FROM

(

SELECT

    'admin2000',

    'adminemail@mail.com',

    '123456'

) AS tmp

WHERE NOT EXISTS

(

SELECT

    admin.admin_username,

    admin.email

FROM

    admin AS admin

WHERE

    admin.admin_username = 'admin2000' AND admin.email = 

'adminemail@mail.com'

UNION

SELECT

staff.staff_username,

staff.email

FROM

staff AS staff

WHERE

staff.staff_username = 'admin2000' AND staff.email = 

'adminemail@mail.com'

 )

LIMIT 1

In the WHERE NOT EXIST part when I only ask for *_username (example: admin_username or staff_username) it works well, but when I need to verify if the email exists too it does not work as intended.

Am I using WHERE NOT EXIST properly? if the username 'admin2000' and email 'adminemail@mail.com' exist on the table 'admin' and I'm trying to insert it into 'visitor' table it inserts it and it should not be doing that.

The problem is the AND in the subqueries. It searches for rows that have that username AND that email. So if you have an admin called admin2000, but with a different e-mail address, that admin won't be returned by the subquery, and so the new row will be inserted.

Use OR instead of AND, and the the problem will be solved.

You seem to want to be writing a query like this:

INSERT INTO visitor (visitor_username, email, PASSWORD)

    SELECT t.*

    FROM (SELECT 'admin2000' as visitor_username, 'adminemail@mail.com' as email, '123456' as PASSWORD

         ) t

    WHERE NOT EXISTS (SELECT 1

                      FROM admin a

                      WHERE a.visitor_username = t.visitor_username AND a.email = t.email

                     )

    UNION 

    SELECT s.staff_username, s.email, ? as password

    FROM staff s

    WHERE s.staff_username = 'admin2000' AND s.email = 

'adminemail@mail.com';

Note that the second subquery is missing a password, so there is an error.

This seems more concisely written using a single query:

INSERT INTO visitor (visitor_username, email, PASSWORD)

    SELECT t.*

    FROM (SELECT 'admin2000' as visitor_username, 'adminemail@mail.com' as email, '123456' as PASSWORD

         ) t

    WHERE NOT EXISTS (SELECT 1

                      FROM admin a

                      WHERE a.admin_username = t.visitor_username AND a.email = t.email

                     ) AND

          NOT EXISTS (SELECT 1

                      FROM staff s

                      WHERE s.staff_username = t.visitor_username AND s.email = t.email

                     );