MySQL Delete-Select row

Question

I'm trying to delete a row from table subscription where there is two foreign Keys (id_user and id_journal). The information that I have is email from table user and nome from table journal. The deleted row needs to match user.email and journal.nome. I can't find a solution. How can I do it?

Table user:

id
name
email
password

Table journal:

id
name

Table Subscription:

id
id_user
id_journal

The last two queries that I tried:

DELETE FROM assinatura WHERE (
SELECT  tbluser.id, journal.id
FROM    tbluser, journal
WHERE email = '$email' AND nome = '$nome')

DELETE FROM assinatura 
INNER JOIN tbluser on (tbluser.email = '$email')
INNER JOIN journal on (journal.nome = '$nome')

I've tried many others queries, but unsuccessful. I think it's important to say that I'm new at MySQL.

Answer 1

DELETE 
FROM Subscription
WHERE id_user IN (
   SELECT      usr.id 
   FROM        user AS usr 
   WHERE       usr.email = INPUT_EMAIL
) 
AND id_journal IN (
   SELECT      jrnl.id 
   FROM        journal AS jrnl 
   WHERE       jrnl.name = INPUT_NAME
)

Answer 2

On another topic ...

Try to avoid excess subscriptions for same user/journal combo by

CREATE TABLE subscription
(
  id int NOT NULL AUTO_INCREMENT primary key,
  id_user int not null,
  id_journal int not null,
  UNIQUE KEY `user_journal_intersect` (`id_user`,`id_journal`)
  -- note Alan stated FK RI in place already
);

U can PK on composite instead, of course (ditching the id column), programmer pref