CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Excluding results from mySQL table

I have a table t1 that has a column t1.subgroup mixed IDs in it. Some are simple numeric values and some have a prefix: a0

2, 554, 9844, a0433, 4363, a0565...

In reality the values like a0433 and a0565 actually correspond to 433 and 565 in another table.

I need to write a query that will exclude certain values from results of t1 when I am given a set of numeric IDs.

SELECT * FROM t1 WHERE t1.group = 2
AND t1.subgroup NOT IN(9844, 433, 565)

How can I do this?

Upvotes: 0

Views: 132

Answers (3)

gbn
gbn

Reputation: 432667

SELECT * FROM t1 WHERE t1.group = 2
AND 
REPLACE(t1.subgroup, 'a0', '') NOT IN (SELECT ThatValue) FROM AnotherTable)

However, this will fail if any values are NULL. So

SELECT * 
FROM 
     t1 
WHERE 
    t1.group = 2
    AND 
    NOT EXISTS (SELECT * FROM AnotherTable A
               WHERE 
               REPLACE(t1.subgroup, 'a0', '') = A.ThatValue)

Edit: added REPLACE, removed CONCAT

Upvotes: 2

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Obviously, your column t1.subgroup is varchar or similar (some textual type), so you would like to write your query as:

SELECT * FROM t1 WHERE t1.group = 2
AND t1.subgroup NOT IN('9844', '433', '565')

Upvotes: 0

xQbert
xQbert

Reputation: 35343

SELECT [your fields] FROM t1 WHERE t1.group = 2
AND replace(t1.subgroup,'a0','') NOT IN(9844, 433, 565)

Though performance may not be the best. It also assumes mysql does an implict conversion on string to int and int to string for this compairison or the replace will need to do the conversion.

Upvotes: 1

Related Questions