CODEWITHSUNDEEP
mysql
Pavlo.G
Pavlo.G

Reputation: 3

MySQL - using column from CASE WHEN into WHERE clause

I want to create a SQL command which will be checking if record exists and:

1) if it exists, it will be equal to 1,

2) if it does not exist, it will be equal to NULL.

And the next step - this value (1 or NULL) I want to use in WHERE clause.

So script should checking if record exist in table_private, if yes, it should search by:

SELECT * FROM `images` WHERE advert_id = 5795 and img_user_id = 1

if not, it should search by:

SELECT * FROM `images` WHERE advert_id = 5795 and img_user_id is NULL

Here is my sql command:

SELECT *, CASE WHEN NOT EXISTS (SELECT * FROM `table_private` WHERE
client_id = 1 AND id = 5795) THEN NULL ELSE 1 END AS exist_value 
FROM `images` WHERE advert_id = 5795 AND img_user_id = exist_value;

But I got:

Nieznana kolumna 'exist_value' w where clause Why? This column is exist - when I remove this part of command: AND img_user_id = exist_value, sql returning correctly this column as resuslt.

Thanks for your help.

Upvotes: 0

Views: 225

Answers (3)

Barmar
Barmar

Reputation: 780663

You can use HAVING to filter based on a calculated value in the SELECT list.

SELECT *, 
        CASE WHEN NOT EXISTS (
                SELECT * 
                FROM `table_private` 
                WHERE client_id = 1 AND id = 5795) 
             THEN NULL 
             ELSE 1 
        END AS exist_value 
FROM `images` WHERE advert_id = 5795
HAVING img_user_id <=> exist_value;

<=> is the null-safe equality operator, it allows comparison with NULL.

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

(Edit: removed stuff no longer applicable after further information gleaned.)

To specifically show the suggestion in the comments for completeness:

SELECT *, CASE WHEN NOT EXISTS (SELECT * FROM `table_private` WHERE
client_id = 1 AND id = 5795) THEN NULL ELSE 1 END AS exist_value 
FROM `images` 
WHERE advert_id = 5795 
AND img_user_id 
    <=> /* exist_value */ 
      CASE WHEN NOT EXISTS (SELECT * 
                            FROM `table_private` 
                            WHERE client_id = 1 AND id = 5795) 
           THEN NULL 
           ELSE 1 
      END
;

Upvotes: 0

Rahul
Rahul

Reputation: 77846

Get the data in a outer query like

SELECT * FROM (
SELECT *, 
CASE WHEN NOT EXISTS (SELECT * FROM `table_private` WHERE
client_id = 1 AND id = 5795) THEN NULL ELSE 1 END AS exist_value 
FROM `images` ) xxx
WHERE advert_id = 5795 
AND img_user_id = exist_value;

Upvotes: 0

Related Questions