Mysql query excluding empty LINES

Asked

Viewed 1,959 times

0

I have to make a query (which will be taken to a PHP application) in the table below. I would like to perform the query in such a way that no lines containing prices (that is to say null) are selected in the gnbr columns up to the sprd. I don’t want to delete lines that are blank (because if tomorrow I need to enter prices on them, I would need to type Cod, Prod, Fab, Qtd and carac again.) I just want to query so that the result set to be sent to php does not receive these lines. the number of lines is variable (there will always be registration of new things) so I cannot do 'where Cod = xxxx;' for example. Can anyone at least go north? grateful.inserir a descrição da imagem aqui

mysql
  • 1

    Make gnbr is not null and so on

  • In the above case which lines would be shown? put an example or at least speak the Cod of such for example would appear!?

  • Also think about reviewing the model , name of unintelligible columns , structure without normalization , a new type of price demands a new column.

  • 1

    the lines shown would be: Cod 7100,7210,7211 and 7212. The ignored lines would be: Cod 7101, 7300,71000 and 72000. Column names are abbreviations of local supermarkets. @Virgilionovic

2 answers

2

Good afternoon. You need to indicate the condition in WHERE. It can be done as follows:

SELECT * FROM produtos WHERE gnbr IS NOT NULL AND sprd IS NOT NULL.

You need to manually add each element with "AND {fieldname} IS NOT NULL".

1

If you want to check if the line has at least one NULL, you can add up all the numeric columns in question and delete the rows in which that sum is NULL:

WHERE (gnbr + vnse + ... + sprd) IS NOT NULL

Or: 

WHERE NOT ISNULL(gnbr + vnse + ... + sprd)

I don’t know about the efficiency of these variations. The sum of the columns certainly has a "weight" on the query, I suggest analyzing a EXPLAIN hers.

If you want to delete only rows where all columns are NULL, can use this:

WHERE COALESCE(gnbr, vnse, ... , sprd) IS NOT NULL

The COALESCE works by establishing a fallbacks. The result will only be NULL if all columns are null.

Browser other questions tagged mysql

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