MYSQL SELECT if else in a subquery with default value in case that a value doesn't exit

Question

I have the next scenario, DB: px

tables:

categories
category_for_level
clients_ip_to_user
full
levels
types
user

I need to check if there is a "url" in the full table which is in one of the categories a specific user has. So I need to first check the user_id then find by the user_id his level and then the categories which are in this level. There is a basic "all" user_id that If I did not find a specific user_id by the client ip address I will use the "all" as a base line.!

The next query is an example of what I am doing right now:

SELECT * FROM full WHERE category IN ( 
        ( 
            SELECT id  from categories WHERE id IN 
            ( 
                ( 
                    SELECT category_id FROM category_for_level WHERE level_id = 
                    (
                        SELECT level FROM user WHERE user_id IN ( 
                            ( 
                                SELECT user_id FROM clients_ip_to_user WHERE ip = "192.168.203.200"
                            )
                        ) 
                    )
                )
            ) 
        ) 
    ) AND url IN ( 'example.com' );

and the result in CSV format is:

"id","url","category","type","y"
3393535,example.com,2,1,1

The user table contains:

"user_id","name","level","y","comment"
1,main,5,1,
2,kids,1,1,
3,all,1,1,

the clients_ip_to_user contains:

"id","ip","user_id","y"
1,"192.168.203.200",2,1
2,all,3,1

What I want to do is that if the subquery: SELECT user_id FROM clients_ip_to_user WHERE ip = "192.168.203.200"

returns nothing then the default "all" will be returned from the user table.

The next query runs by itself:

IF ( SELECT COUNT(*) FROM px.clients_ip_to_user WHERE ip = "192.168.203.200" ) > 0 THEN
  SELECT user_id FROM px.clients_ip_to_user WHERE ip = "192.168.203.200";
ELSE 
  SELECT user_id FROM px.clients_ip_to_user WHERE ip = 'all';
END IF;

but not as a subquery which I tried to replace the: SELECT user_id FROM clients_ip_to_user WHERE ip = "192.168.203.200" with.

I am not a SQL expert but I assumed that it will work. I have tried couple other ways to implement this and it works inside a stored procedure. With stored procedure I do not now how to pass an array of domains. For example I need to test not only "example.com" but the array '("www.example.com", ".www.example.com", ".example.com", ".com")'

I tried to use some combinations of the next stored procedure but I couldn't make it:

CREATE PROCEDURE px.findDomainByUserIp(
    IN clientIP VARCHAR(255),
    IN urlsArray VARCHAR(255)
)
BEGIN

SET @ip:="all";

IF ( SELECT COUNT(*) FROM px.clients_ip_to_user WHERE ip = clientIP ) > 0 THEN
    SET @ip:= clientIP ;
END IF;

SELECT @ip;

SELECT * FROM full WHERE category IN ( 
        ( 
            SELECT id  from categories WHERE id IN 
            ( 
                ( 
                    SELECT category_id FROM category_for_level WHERE level_id = 
                    (
                        SELECT level FROM user WHERE user_id IN ( 
                            ( 
                                SELECT user_id FROM clients_ip_to_user WHERE ip = @ip
                            )
                        ) 
                    )
                )
            ) 
        ) 
    ) AND FIND_IN_SET(url, urlsArray );
END

Hope you can help :D

Answer 1

I'm bit lost in the two meanings of "all". Suggest you change the spelling of one of them.

Maybe...

SELECT * FROM px.clients_ip_to_user
    WHERE ip IN ("192.168.203.200", "all");

That assumes "all" is a valid value in the ip column.

If, instead, the user.name = "all" is supposed to mean something, then maybe

SELECT user.name
       FROM clients_ip_to_user
       JOIN user  USING (user_id)

IFNULL

See also the COALESCE(possibly_null_expression, "default value")