CODEWITHSUNDEEP
mysqlsql
Appel Flap
Appel Flap

Reputation: 271

SQL - Select from table, if user_id is 0 select from different table

I'm currently struggling with a SQL query, I have 2 tables, clients and clientbook.

The context

clientbook is the table where there will always be a client entry:

id | firstname | lastname | user_id | saloon_id
-----------------------------------------------
1    lorem       ipsum      5         10
2    jack        inthebox   0         10

clients may or may not have an entry, based on if the client was logged in or not at the time of reservation. If clientbook.user_id is 0, the client won't be in clients table. clients looks like this:

user_id | firstname | lastname
------------------------------
5         lorem       ipsum

The question

I want to make the following query:

If clientbook.user_id is not 0, then query clients and select the clients.firstname and clients.lastname from there.

If clientbook.user_id is 0, select the clientbook.firstname and clientbook.lastname.

What I currently have

I have this query:

    SELECT 
        c.firstname,
        c.lastname
    FROM
        clients AS c
    INNER JOIN
        clientbook AS cb
    ON
        c.id = cb.user_id
    WHERE
        c.firstname LIKE CONCAT("%", 'lorem', "%")
    AND
        cb.saloon_id = 10

However, this will only select from clients if there is a row present. If no row exists, it will not select from clientbook.

How can I make this query?

Upvotes: 0

Views: 287

Answers (3)

Filipe Ar
Filipe Ar

Reputation: 21

I understood you want to bring the names from clientbooks table if you do have the matching row on client table. To do that you can combine a CASE and a Left join. Although you could also creat a sub-select or Union all query to bring them altogether. The detail is you should join them by “user_id” column.

SELECT ( CASE WHEN cb.user_id = 0 THEN cb.firstname ELSE c.firstname END ) AS firstname ,( CASE WHEN cb.user_id = 0 THEN cb.lastname ELSE c.lastname END ) AS lastname FROM clientbook AS cb LEFT JOIN clients AS c ON c.user_id = cb.user_id WHERE c.firstname LIKE CONCAT ( "%" ,'lorem' ,"%" ) AND cb.saloon_id = 10

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

 create table clientbook(id int, firstname varchar(50), lastname varchar(50), user_id int, saloon_id int);
 insert into clientbook values(1,    'lorem',  'ipsum',      5,         10);
 insert into clientbook values(2,    'jack',   'inthebox',   0,         10);
 
 create table clients (user_id int, firstname varchar(50), lastname varchar(50));
 insert into clients values(5,         'lorem','ipsum'   );

Query:

   SELECT 
         (case when cb.user_id=0 then cb.firstname else c.firstname end)firstname,
         (case when cb.user_id=0 then cb.lastname else c.lastname end)lastname
     FROM
         clientbook AS cb
     Left JOIN
         clients AS c
     ON
         c.user_id = cb.user_id
    
     AND
         cb.saloon_id = 10

Ouput:

firstname lastname
lorem ipsum
jack inthebox

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If clientbook.user_id is not 0, then query clients and select the clients.firstname and clients.lastname from there.

If clientbook.user_id is 0, select the clientbook.firstname and clientbook.lastname.

Based on this description, you want a left join

select coalesce(c.firstname, cb.firstname) as firstname,
       coalesce(c.lastname, cb.lastname) as lastname
from clientbook cb left join
     clients c
     on cb.user_id = c.user_id

Upvotes: 4

Related Questions