Conditional insert using WHERE NOT EXISTS

[nav33n]

Hi all ! I have been assigned a task to implement an insert query into table a only if a record doesn't exist in table c. I have been scratching my head for past 2 days and even my best friend google didn't help me much, so I am asking the question here.
So, here it is.
I have created dummy tables to simplify the table structure.

CREATE TABLE `test2`.`a` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`age` INT NOT NULL ,
`postcode` VARCHAR( 20 ) NOT NULL ,
`city` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 




 CREATE TABLE `test2`.`b` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`age` INT NOT NULL ,
`postcode` VARCHAR( 20 ) NOT NULL ,
`city` VARCHAR( 20 ) NOT NULL ,
`country` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `test2`.`b` (`counter` ,`name` ,`age` ,`postcode` ,`city` ,`country`)
VALUES (NULL , 'testuser1', '25', '123456', 'Some city', 'Some country'), 
(NULL , 'testuser2', '26', '234567', 'Some city', 'Some country');



 CREATE TABLE `test2`.`c` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id` INT NOT NULL ,
`location` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 

INSERT INTO `test2`.`c` (`counter` ,`id` ,`location`)
VALUES (NULL , '1', 'location1'),
(NULL , '2', 'location2');


 CREATE TABLE `test2`.`d` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`c_id` INT NOT NULL ,
`Department` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 

INSERT INTO `test2`.`d` (`counter` ,`c_id` ,`Department`)
VALUES (NULL , '1', 'department1'),
(NULL , '1', 'department2');

And here is the query.

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

The above query works perfectly fine. Let me explain what it does. If the last query (ie., after WHERE NOT EXISTS ) doesn't return any row, then it insert all the record(s) to table a from table b. Simple.. No problem..
The problem is, IF I want to insert a record to table a from table b on certain condition, ie.,

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b where counter = 1
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

this doesn't work! The following query doesn't work too.

INSERT INTO a (name, age, postcode, city ) 
(SELECT name, age, postcode, city 
FROM b where counter = 1) 
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

However, a simple query like

INSERT INTO a( name, age, postcode, city )
SELECT name, age, postcode, city
FROM b
WHERE counter =1

works fine. I am at my wit's end trying to find a solution!

I hope I have framed the question well enough. Any help will be appreciated ! :) Thanks in advance.

[nav33n]

:icon_redface: After numerous attempts, I found out the solution. Here it is.

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b where counter = 1
AND NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

Thanks for looking ;)