CODEWITHSUNDEEP
mysqlnotin
borewik
borewik

Reputation: 83

Selecting rows without few values in different columns ( NOT IN )

So first of all, I manged to do something like this:

SELECT * FROM `trades`
WHERE 15003 NOT IN (`slot1`, `slot2`, `slot3`, `slot4`)

And this works correct, gives me rowes without product '15003' in any of those columns. But what if I don't want to have any of '15003' or '15004' or '15008' those in query? I want to do it like this:

SELECT * FROM `trades`
WHERE 15003 NOT IN (`slot1`, `slot2`, `slot3`, `slot4`) AND
15004 NOT IN (`slot1`, `slot2`, `slot3`, `slot4`) AND
15008 NOT IN (`slot1`, `slot2`, `slot3`, `slot4`)

It works, but I think its not proper...

How can I optimize this?

Edit: Each trade has its id and 4 slots.

Upvotes: 0

Views: 85

Answers (2)

Sammitch
Sammitch

Reputation: 32242

This is a very poor design choice, exemplified by how much difficulty you're having with this query. Instead of:

TABLE trades
  slot1 INT
  slot2 INT
  slot3 INT
  slot4 INT

should be properly normalized to something like:

TABLE trades
  trade_id INT

TABLE trades_slots
  trade_id INT
  slot_id INT

Which will allow you much more flexibility and make the queries MUCH easier to write.

SELECT *
FROM trades t INNER JOIN trades_slots s
  on t.trade_id = s.trade_id
WHERE s.slot_id NOT IN (15003, 15004, ...)

Upvotes: 1

kasi
kasi

Reputation: 774

Use a query like this:

Select *, GROUP_CONCAT(product1,product2,product3,product4) as prods
from Trades
group by ID
having prods not regexp '15003|15004|15008'

Upvotes: 0

Related Questions