CODEWITHSUNDEEP
mysqlsql
Jessie A. Morris
Jessie A. Morris

Reputation: 2317

Ignore a column based on where clause

Edit:

So, it seems there is some confusion in specifically what I want to do, so I am going to alter my example.

I have a table named example_table:

id       code1      code2
-------------------------
1        1780       4245
2        1781       4280
3        1900       1200
4        2800       4217
5        2817       1782

With this, I want to find something like,

SELECT * FROM example_table WHERE code1 LIKE '%17%' OR code2 LIKE '%17%';

I want this to return

id      code1      code2
------------------------
1       1780       null
2       1781       null
4       null       4217
5       2817       1782

So you'll notice it nulls any result that doesn't match from either column, and if both are present it will grab both.

Upvotes: 3

Views: 1224

Answers (4)

MatBailie
MatBailie

Reputation: 86706

Literally, you could do something like this...

SELECT
  first_name,
  CASE WHEN last_name = 'Smith' THEN last_name ELSE maiden_name END
FROM
  users
WHERE
     last_name   = 'Smith'
  OR maiden_name = 'Smith'

But, actually, whatever field matches, it'll be 'Smith', so why bother, just do this?

SELECT
  first_name,
  'Smith'
FROM
  users
WHERE
     last_name   = 'Smith'
  OR maiden_name = 'Smith'


EDIT: Based on your editted question...

SELECT
  id,
  CASE WHEN code1 LIKE '%17%' THEN code1 ELSE NULL END AS code1,
  CASE WHEN code2 LIKE '%17%' THEN code2 ELSE NULL END AS code2
FROM
  example_table
WHERE
     code1 LIKE '%17%'
  OR code2 LIKE '%17%'
;

Upvotes: 5

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

This SQL code should do it for you

SELECT
    A.id,B.code1,C.code2
FROM
    (SELECT id FROM example_table) A
    LEFT JOIN
    (
        SELECT id,code1 FROM example_table
        WHERE code1 LIKE '%17%'
    ) B ON A.id=B.id
    LEFT JOIN
    (
        SELECT id,code2 FROM example_table
        WHERE code2 LIKE '%17%'
    ) C ON A.id=C.id
WHERE
    ISNULL(B.code1) = 0 OR ISNULL(C.code1) = 0
;

I cannot make any promises on performance for it.

If you do not mind presenting null as a string, try this one:

SELECT
    id,
    IF(code1 LIKE '%17%',code1,'null') code1,
    IF(code2 LIKE '%17%',code2,'null') code2
FROM example_table WHERE
    code1 LIKE '%17%' OR
    code2 LIKE '%17%';

One full table scan with no joins. This may be better.

Upvotes: 1

Asaph
Asaph

Reputation: 162781

You can do this elegantly with a UNION:

SELECT first_name, last_name FROM users WHERE last_name='Smith'
    UNION
SELECT first_name, maiden_name FROM users WHERE maiden_name='Smith';

or somewhat less elegantly with a CASE statement:

SELECT first_name, 
    CASE WHEN last_name='Smith' THEN last_name
    ELSE maiden_name END
    AS last_name
FROM users where last_name='Smith' or maiden_name='Smith';

which as @Dems points out, can be simplified to:

SELECT first_name, 'Smith' AS last_name
FROM users WHERE last_name='Smith' or maiden_name='Smith';

which begs the question, do you really even need last_name in your result set at all? The caller already knows the last_name because it's part of the query. Why not return only the first_name and have a very simple, easy to understand query?

SELECT first_name FROM users
WHERE last_name='Smith' or maiden_name='Smith';

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65274

Do you mean

SELECT
  first_name,
  IF(last_name='Smith',last_name,concat(last_name,' (née ',maiden_name,')')) AS last_name
FROM
  users
WHERE
  users.last_name='Smith'
  OR maiden_name='Smith'

Upvotes: 0

Related Questions