Deleting records from mysql with a join
<p>Here are two tables:</p>

<pre><code>accounts
+-----------+-----------+--------+
| accountid | account   | userid |
+-----------+-----------+--------+
|         1 | blah1     |     1  |
|         2 | blah2     |     2  |
|         3 | blah3     |     3  |
|         4 | 2nd4user1 |     1  |
+-----------+-----------+--------+

links
+-------------+-----------+--------+
| usergroupid | accountid | userid |
+-------------+-----------+--------+
|           1 | 4         | 3      | //Links user 3 on user1's account 4. 
|           2 | 2         | 3      | //Links user 3 on user 2's account 2.
|           3 | 3         | 1      | //Links user 1 on user 3's account 3
+-------------+-----------+--------+
</code></pre>

<p>Is it possible to write a single sql query that can delete based on a known usergroupid and a known userid, where the userid could be in <strong>either</strong> of the two tables. </p>

<p>I am listing out all accounts for each user using a left join, and then have a delete link that is based on the usergroupid. For each of these, the userid will be the same but the usergroupid will differ.</p>

<p>For example, a combination of:</p>

<p><code>Delete from table2 where usergroupid = 3 and userid = 1.</code> (remove user 1 from user 3's account 3)</p>

<p>AND </p>

<p><code>DELETE from table2 where usergroupid = 1 and accounts.userid = 1</code> (remove user 3 from user 1s account 4)</p>

<p>I've tried to inner join a left joins and selecting distinct, but can't get it to work! Clearly I could just write two different queries, but I really want to do this in one go - if I can. What am I doing wrong? </p>

<pre><code>DELETE links FROM links INNER JOIN accounts ON accounts.accountid = 
links.accountid WHERE links.usergroupid = 30 AND (accounts.userid = 1 OR 
links.userid = 1) LIMIT 1

DELETE links FROM links ON 'accountid' IN (SELECT 'accountid' FROM 'accounts')          
WHERE links.usergroupid = :usergroupid AND (accounts.userid = :userid OR    
links.userid = :userid)

DELETE FROM links WHERE 'accountid' IN (SELECT 'accountid' FROM 'accounts'     
INNER JOIN 'links' on 'accounts.accountid' = 'links.accountid') WHERE    
'links.usergroupid' = :usergroupid AND ('accounts.userid' = :userid OR 'links.userid' = :userid)   
LIMIT 1

DELETE FROM t1 USING links t1 INNER JOIN accounts t2 ON (t1.accountid = 
t2.accountid) WHERE WHERE links.usergroupid = :usergroupid AND (accounts.userid = :userid OR    
links.userid = :userid) LIMIT 1
</code></pre>