Home / Questions / 320788
Accepted
Caldeiro
Asked: 2020-01-10 08:36:28 +0800 CST 2020-01-10 08:36:28 +0800 CST

Where mysql can be null

  • 772

Once this is done as an example, as you will see in the select I look for the id of the offer, the name of the client, and the status of the offer.

Said query status must be 2 . How can I add to the where so that the client can be null. I know that I ask for the name of the client and if it is null it will not have it, that is what I want. Tell me all the offers with status 2 have a client or not. The problem is that if I have a client I have to know the name

create table clientes(Cliente_id integer primary key, Nombre varchar(50), 
Direccion varchar(100), NIF integer);
create table ofertas(id integer primary key, codigo_oferta 
integer,cliente_id integer, fecha date, estado integer, 
CONSTRAINT fk_Cliente FOREIGN KEY (cliente_id) REFERENCES clientes 
(Cliente_id));

show tables;

insert into clientes values (1,'Luis','asdaddasdsda',23858870);
insert into clientes values (2,"Maria","Daigonal 234",12345678);

insert into ofertas values (1,00001,1,default,2);
insert into ofertas values (2,00002,1,default,1);
insert into ofertas values (3,00003,null,default,2);
insert into ofertas values (4,00004,null,default,1);

Select O.id, C.Nombre, O.estado from ofertas O, clientes C where 
C.Cliente_id = O.cliente_id  and O.estado = 2 \G
mysql

1 Answers

  1. Best Answer

    I have done the following test and it worked for me.

    Simply, you do a left join to the clients table relating by client_id.

    SELECT O.id, C.Nombre, O.estado FROM ofertas O
    LEFT JOIN clientes C ON c.cliente_id = o.cliente_id
WHERE O.estado = 2

    And the result:

    enter image description here

    • 2