CODEWITHSUNDEEP
mysql
Frank
Frank

Reputation: 624

Select all rows from a table where no columns are empty

enter image description here

When I use this query: 

SELECT * from table

empty results are displayed as well.

When I use: 

SELECT * from table WHERE tablet<>''

I get the result I want to achieve, but only for tablet. I would like to achieve this for the whole table.

Something like: 

SELECT * from table //that is not empty

Any ideas? Thanks!

Upvotes: 2

Views: 2300

Answers (4)

T McKeown
T McKeown

Reputation: 12847

I think you are looking for a single list with 2 columns:

SELECT `Handy` AS Category,
T.Handy AS Description
FROM Table AS T
WHERE T.Handy <> ''
UNION ALL
SELECT `PC` AS Category,
T.PC AS Description
FROM Table AS T
WHERE T.PC <> ''
UNION ALL
SELECT `Notebook` AS Category,
T.Notebook AS Description
FROM Table AS T
WHERE T.Notebook <> ''
UNION ALL
SELECT `Tablet` AS Category,
T.Tablet AS Description
FROM Table AS T
WHERE T.Tablet <> ''
ORDER BY Category

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16691

A neat query using only a where clause would be to use the LEAST() function, and make sure that the smallest value is not empty, like this:

SELECT *
FROM myTable
WHERE LEAST(handy, pc, notebook, tablet) <> '';

EDIT

Works in Fiddle.

Upvotes: 0

Mureinik
Mureinik

Reputation: 310983

You could use a bunch of != '' conditions:

SELECT *
FROM   table
WHERE  handy != '' AND
       pc != '' AND
       notebook != '' AND
       tablet != ''

Upvotes: 0

Robbert
Robbert

Reputation: 6582

The only way to do this is to use an and statement for each field

SELECT * from table WHERE tablet<>'' AND handy<>'' AND pc<>''

(Sorry, my work blocks imgr.com so I can't see your screenshot, and don't know all the columns in your table).

Upvotes: 0

Related Questions