1

Here are two tables:

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
+-------------+-----------+--------+

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 either of the two tables.

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.

For example, a combination of:

Delete from table2 where usergroupid = 3 and userid = 1. (remove user 1 from user 3's account 3)

AND

DELETE from table2 where usergroupid = 1 and accounts.userid = 1 (remove user 3 from user 1s account 4)

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? 

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
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Simeon
  • 848
  • 1
  • 11
  • 34
  • 2
    Anything helpful here? http://stackoverflow.com/questions/652770/delete-with-join-in-mysql – Shomz May 02 '16 at 22:45
  • 1
    pay attention to replication though. Some replication solutions can go mad about updated/deletes with joins. – strangeqargo May 02 '16 at 22:58
  • Thanks - I have looked at some things on those lines; the difference here is that the OR condition seems to break it. – Simeon May 02 '16 at 23:39
  • 2
    What's the need / desire to do it in a single query? It's often FAR more readable to see something like this in two, distinct, clean, easy-to-follow queries. – random_user_name May 02 '16 at 23:45

0 Answers0