Your statement seems to be correct. It could also be written like this:
DELETE ii
FROM inventoryitems AS ii
WHERE ii.itemid = 2340000
AND EXISTS
( SELECT *
FROM characters AS c
WHERE c.characterid = ii.characterid
AND EXISTS
( SELECT *
FROM accounts AS a
WHERE a.id = c.accountid
AND a.banned = 1
)
) ;
One thing that may be causing this is if you have a character related to many accounts and one of them has banned = 1
while the other have banned = 0
. I assume you want the deletion to happen (not with just one but) only if all the related accounts have banned = 1
. We can modify the above code to:
DELETE ii
FROM inventoryitems AS ii
WHERE ii.itemid = 2340000
AND EXISTS
( SELECT *
FROM characters AS c
WHERE c.characterid = ii.characterid
AND EXISTS
( SELECT *
FROM accounts AS a
WHERE a.id = c.accountid
AND a.banned = 1
)
AND NOT EXISTS
( SELECT *
FROM accounts AS a
WHERE a.id = c.accountid
AND a.banned = 0
)
) ;
or simpler to:
DELETE ii
FROM inventoryitems AS ii
WHERE ii.itemid = 2340000
AND EXISTS
( SELECT *
FROM characters AS c
JOIN accounts AS a
ON a.id = c.accountid
WHERE c.characterid = ii.characterid
HAVING MIN(a.banned) = 1
) ;
After the clarifications, all the above are void. The problem was that characters
table does not have characterid
column but only id
. So the statement used by the OP was translated/parsed as:
DELETE FROM inventoryitems
WHERE characterid IN
(SELECT inventoryitems.characterid -- notice this
from characters
WHERE accountid IN
(SELECT id
from accounts
WHERE banned = '1'
)
)
AND itemid = '2340000';
which makes the subquery uncorrelated and means "delete all rows with itemid = 2340000
" if there exists at least one row (any row, not necessarily related) in the accounts with banned=1
"
That's one reason why it's good to always (*) write columns with their full name as tablename.columnname
or tablealias.columnname
(an error would have been thrown if you had done this and problem would have been solved faster.)
(*) Unless one wants this behaviour to occur, which is a rather extreme case.
What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT. If you are willing to store the results in a temp table, I got just the thing.
First, here is the code to use you sample data in a table called prod
and a temp table called prodcat
to hold the results you are looking for.
use test
drop table if exists prod;
drop table if exists prodcat;
create table prod
(
product_id int not null,
categories varchar(255)
) engine=MyISAM;
create table prodcat
(
product_id int not null,
cat int not null
) engine=MyISAM;
insert into prod values
(10,'9,12'),(11,'8'),(12,'11,18,5');
select * from prod;
Here it is loaded
mysql> use test
Database changed
mysql> drop table if exists prod;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists prodcat;
Query OK, 0 rows affected (0.00 sec)
mysql> create table prod
-> (
-> product_id int not null,
-> categories varchar(255)
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> create table prodcat
-> (
-> product_id int not null,
-> cat int not null
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into prod values
-> (10,'9,12'),(11,'8'),(12,'11,18,5');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from prod;
+------------+------------+
| product_id | categories |
+------------+------------+
| 10 | 9,12 |
| 11 | 8 |
| 12 | 11,18,5 |
+------------+------------+
3 rows in set (0.00 sec)
mysql>
OK, you need query to put together each product_id with each category. Here it is:
select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
Here it is executed
mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
-> replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
+----------------------------------------------------------------------------------------------------------------------------------+
| ProdCatQueries |
+----------------------------------------------------------------------------------------------------------------------------------+
| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL; |
| insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL; |
| insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL; |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
Let me run each line by hand
mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
OK, good. The queries work. Did the prodcat table populate properly?
mysql> select * from prodcat;
+------------+-----+
| product_id | cat |
+------------+-----+
| 10 | 9 |
| 10 | 12 |
| 11 | 8 |
| 12 | 11 |
| 12 | 18 |
| 12 | 5 |
+------------+-----+
6 rows in set (0.00 sec)
mysql>
OK Great. It has the data.
To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table.
I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. If your actual query had 1000s of rows, a single MySQL would not have been practical.
Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. Then, you have a table with the products and categories combinations individually written.
Best Answer
WITHOUT INDEXING
or
WITH INDEXING
Create a FULLTEXT index on
film_info
Query using the
FULLTEXT
index