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

Question

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.

it really depends on your table structure and what mysql explain states. what does explain show for this query and the previous? — Michael, Oct 24 '12 at 19:13
Just add EXPLAIN key word before your query and post the result — sdespont, Oct 24 '12 at 19:21
CAUTION: Poorly normalized data structure ahead. Expect difficult query syntax. — Joe Stefanelli, Oct 24 '12 at 19:21

Sammitch · Accepted Answer · 2012-10-24T20:18:04.723

1

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, ...)

edited Oct 24 '12 at 20:18

answered Oct 24 '12 at 19:38

Sammitch

30,782
7
50
77

Hymm.. But it is INT already, not varchars. Do you mean I shouldn't use '' ? It is normalized like this, but I gave it in different form in example. – borewik Oct 24 '12 at 19:50
I must have been seeing things, I thought there were quotes around your numbers. And if it is normalized like this already then why is your query so broken? – Sammitch Oct 24 '12 at 20:17

kasi · Answer 2 · 2012-10-24T20:01:30.470

0

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'

edited Oct 24 '12 at 20:01

answered Oct 24 '12 at 19:45

kasi

774
4
9

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

How can I optimize this?

2 Answers2