SELECT to filter null value? [duplicate]

Navigation
0

I'm trying to make a SELECT in a conta table, in this table I have a foreign key for the venda table, in certain cases this foreign key is null and I want to return the information of the sale when this key is not null and ignore when it is, I am trying to do this through a left join , but I am not getting it.

How to do this?

I'm trying like this. 

select * from conta t1
inner join cliente t2 on (t2.id = t1.cliente_id)
left join (
    select * from venda t3 
    inner join itemvenda t4 on (t4.venda_id = t3.id)
    inner join produto t5 on (t4.produto_id = t5.id)    
    ) ON ((t1.venda_id is not null) AND t1.venda_id = t3.id)
where (t1.status = 0) AND (t1.tipoConta = 1) AND (t1.dtVencimento BETWEEN "2016/10/01" AND "2016/10/28")
ORDER BY t2.nome, t1.dtVencimento

Exception 

select * from conta t1
inner join cliente t2 on (t2.id = t1.cliente_id)
left join (
    select * from venda t3 
    inner join itemvenda t4 on (t4.venda_id = t3.id)
    inner join produto t5 on (t4.produto_id = t5.id)    
    ) ON ((t1.venda_id IS NOT NULL) AND t1.venda_id = t3.id)
where (t1.status = 0) AND (t1.tipoConta = 1) AND (t1.dtVencimento BETWEEN "2016/10/01" AND "2016/10/28")
ORDER BY t2.nome, t1.dtVencimento LIMIT 0, 25
Mensagens do MySQL : Documentação

#1248 - Every derived table must have its own alias
    
asked by anonymous 30.10.2016 / 01:17

1 answer

1

The left join block must have an alias. Modify your query by adding an alias as follows: 

left join (
    select * from venda t3 
    inner join itemvenda t4 on (t4.venda_id = t3.id)
    inner join produto t5 on (t4.produto_id = t5.id)    
    ) nomedoalias ON ((t1.venda_id IS NOT NULL) AND t1.venda_id = t3.id)

In nomedoalias you change to the desired alias.

    
30.10.2016 / 20:43
Change default project directory made in Zend 2 Prevent the page from being displayed only when all the elements of the page are loaded