I have two mysql tables sent and queue. I insert members into the queue table. However I don't want to insert that same member again--if they're in the sent table. They were already sent an invitation.
How can I check if the member inserted into queue doesn't exist in sent ?
INSERT
INTO queue (member)
SELECT $member
FROM dual
WHERE $member NOT IN
(
SELECT member
FROM sent
)
If it's your application principle, that there must not be identical queue entries, why not add a unique constraint (on member_email, member_id or whatever the identifier might be) to your queue table? You can then insert your data with REPLACE INTO ... which in fact means: If there's an existing value with the same primary key or unique key, it will be deleted before inserting the new one.
REPLACE INTO queue (member_email, queue_detail)
VALUES ('already_existing_email', 'some_data');
This would virtually UPDATE the entry of already_existing_email with 'some_data'.
If there's no entry *already_existing_email*, data will be inserted.
Try to select the user from the sent table and if there are no rows returned then insert into the queue table. Otherwise do nothing. For any more depth than that you would need to disclose more of your schema and what denotes a user as unique (email address?).
