SQL with conditional where

Question

Could someone please assist with the below query ?

I have a table like below :

Code        alias   user
--------    -----   -------
7305553     BPP     (null)
8136852     BPP     AYU
8136852     BPP     TKL
7305553     BPFX    (null)
8136848     BPFX    YAO
11903927    CIX     (null)

And I want to retrieve the "Code" value by passing "alias" and "user" values, however when the "user" doesn't match/exist, I'd need the row with the null user. Eg :

select Code from my_table where alias = 'BPP' and user = 'TEST'

should return the 1st line code value (7305553) , as user "TEST" doesn't exist. Is this kind of best-matching behavior possible with some kind of conditional "where" ? (with some kind of case statement?)

select Code from my_table where alias = 'BPP' and (user = 'TEST' OR user IS NULL) ORDER by user ASC; Then you get one or two rows. Just adjust the ORDER clause to get the NULL as a second record when you have two ones, so you are sure which one is the NULL. — Anton Mitsev
– Anton Mitsev, Commented Jul 2, 2019 at 8:32

Popeye · Accepted Answer · 2019-07-02 08:35:34Z

2

Try this:

SELECT
    CODE
FROM
    MY_TABLE
WHERE
    ALIAS = 'BPP'
    AND (USER = 'TEST' OR USER IS NULL)
    ORDER BY USER NULLS LAST
    FETCH FIRST ROWS ONLY

Cheers!!

answered Jul 2, 2019 at 8:35

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nullPointer Over a year ago

Thanks very much. Best answer I think

Ely · Accepted Answer · 2019-07-02 08:43:58Z

0

Retrieve the row if the alias equals 'BPP' and the user equals 'TEST'
OR
The alias equals 'BPP' and the user IS NULL and there is no alias 'BPP' and user 'TEST'

select code from my_table where 
(alias = 'BPP' and user = 'TEST')
or
(alias = 'BPP' and user IS NULL and NOT EXIST (select 'X' from my_table where alias = 'BPP' and user = 'TEST'))

answered Jul 2, 2019 at 8:43

Ely

11.2k5 gold badges45 silver badges69 bronze badges

Comments

shiva · Accepted Answer · 2019-07-02 09:27:28Z

0

Try CASE in MySQL :

SELECT
    CODE
FROM
    MY_TABLE
WHERE
    ALIAS = 'BPP'
AND 
    CASE 
        WHEN USER != 'TEST' THEN 1=1 
        ELSE USER = 'TEST'
    END;

Hope this help.

edited Jul 2, 2019 at 9:27

answered Jul 2, 2019 at 8:42

shiva

4291 gold badge7 silver badges21 bronze badges

4 Comments

nullPointer Over a year ago

thanks for the suggestion, however as per the tag I'm on oracle and not mysql

nullPointer Over a year ago

unfortunately it doesn't : ORA-00905: missing keyword

nullPointer Over a year ago

I already tried it with "END" as well on my side, still same error

shiva Over a year ago

Hope, There will be another syntax. I haven't Oracle DB so that i can't check for oracle.

Collectives™ on Stack Overflow

SQL with conditional where

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related