SELECT to filter null value?

Asked

Viewed 1,197 times

0

I’m trying to make a SELECT in a table conta, in this table I have a foreign key to the table venda, 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’m 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
mysql sql
  • 1

    lacked a as on the part of left Join () as c and continues... has an error of t3 is not the correct alias would be: ) as c ON ((t1.venda_id IS NOT NULL) AND t1.venda_id = c.id)

  • @Bacco apparently this duplicate does not appear to be the best situation I need.

  • Note that there is only effective closure if other people have the same impression and vote the same way, but even so you can always [Dit] the question to add details that differentiate more, or even leave a comment for us to know what was the problem with the IS NULL. Closure is an organization tool, not a penalty tool. The idea is that one way or another you get out of here with the problem solved. But for this, you need to detail the exact difficulty, so that we know how to help. The first step would be to fix the missing Alias (AS nome).

  • 2

    No doubt the error is in the alias. .. You are trying to access the T3 that is within another context.

  • 2

    I don’t know what was your logic for making a left Join with a sub select being that you didn’t make any filter or selected only some fields of the tables, your select could have been foito with left Join straight into the tables without the need for su subselect...

1 answer

1

The left Join block needs to 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 modify to the desired alias.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.