MYSQL Returning result without empty fields

Question

The following code below gives me a result of

enter image description here

but how do I make it so that I get a result of

enter image description here

Code:

SELECT * FROM `shop_shipping_rules` LEFT JOIN `countries` ON `shop_shipping_rules`.shop_shipping_rule_country_iso = `countries`.iso 
LEFT JOIN `shop_shipping_regions` ON `shop_shipping_regions`.shop_shipping_rule_region_code = `shop_shipping_rules`.shop_shipping_rule_region_code
WHERE `website_id` = 64 AND `shop_shipping_rule_name` IS NOT NULL

Kermit · Accepted Answer · 2013-11-20 22:41:42Z

2

In your predicate, filter out empty fields.

AND `shop_shipping_rule_name` IS NOT NULL AND `shop_shipping_rule_name` <> ''

It's also best practice to stay away from SELECT * and use a column list. Also, you should use aliases and be explicit with your columns.

You will probably want to go through these and make the empty values NULL:

UPDATE `shop_shipping_rules` 
SET `shop_shipping_rule_name` = NULL 
WHERE `shop_shipping_rule_name` = ''

answered Nov 20, 2013 at 22:41

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

OR should be AND.

Collectives™ on Stack Overflow

MYSQL Returning result without empty fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related