MySQL SubQuery - MySQL 3.23

Question

I've 2 tables, emp_master and passport_details.

emp_master(emp_id,first_name,email_id,dob,doj,status.........)
passport_details(id, emp_id,passport_number,given_name,......).

I am trying to fetch emp_id and first_name from emp_master who have not entered passport_details.

I tried different combination of SubQueries, used NOT IN, NOT EXISTS.

SELECT emp_id,first_name
FROM emp_master
WHERE emp_id NOT IN(SELECT emp_id FROM passport_details WHERE status=1);

I am getting error

You have an error in your SQL syntax near 'SELECT emp_id FROM passport_details WHERE status=1)' at line 3

I am using MySQL 3.23.

My question is

1. Do MySQL 3.23 supports SubQueries?
2. What could be the optimal query to fetch emp_id and first_name from emp_master who have not entered passport_details.

Answer 1

1.) Subqueries were added in MySQL 4.1 SubQuery in MySQL

You can rewrite your query into JOIN statement like this.

SELECT a.emp_id,
       a.first_name
FROM emp_master a 
        LEFT JOIN passport_details b
           on a.emp_id = b.emp_id
WHERE a.`Status` = 1 AND
      b.emp_id IS NULL;

Answer 2

No, subqueries are not supported until 4.1: http://dev.mysql.com/doc/refman/4.1/en/mysql-nutshell.html

Answer 3

select em.emp_id, em.first_name
from emp_master em left join passport_details pd
    on pd.emp_id = em.emp_id and pd.status = 1
where pd.emp_id is null

I don't have a 3.23 instance to test with, but this should work.

Answer 4

A quick Google suggests that subqueries were brought in in MySql 4.1. So they're not supported in 3.23.

Thy something along these lines instead:

SELECT emp_id,first_name
FROM emp_master
JOIN passport_details ON emp_id
WHERE status = 1;