CODEWITHSUNDEEP
mysqlsql
Joe
Joe

Reputation: 6583

How do you run an SQL query to return even if the field is EMPTY

Currently I'm running this SQL query to retrieve custom posts for a store locator. Everything is fine except I would like for it to return items even if the "telephone" field is empty. As of now it will only pull in the ones that have that field filled in.

This is how my query looks when run:

my query

Anyone have a clue? (Also is there a more efficient way to run this?)

SELECT   wp_posts.post_title as name, 

        address.meta_value as address,
        latitude.meta_value as lat,
        longitude.meta_value as lng,
        telephone.meta_value as phone,


        ( 3959 * acos(
        cos( radians( '%s' ) ) *
        cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
        cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) - radians( '%s' ) ) +
        sin( radians( '%s' ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )
         ) ) AS distance

        FROM wp_postmeta as address, wp_postmeta as latitude, wp_postmeta as longitude, wp_postmeta as telephone,
         wp_posts

WHERE   
    (wp_posts.ID = address.post_id
        AND address.meta_key = '_dealer_address' )  

AND     (wp_posts.ID = latitude.post_id
        AND latitude.meta_key = '_dealer_latitude' )

AND     (wp_posts.ID = longitude.post_id
        AND longitude.meta_key = '_dealer_longitude' )      

AND     (wp_posts.ID = telephone.post_id
        AND telephone.meta_key = '_dealer_telephone' )

Upvotes: 0

Views: 396

Answers (3)

Joe
Joe

Reputation: 6583

Posting the answer as per Marc B's suggestion from his comment.

     SELECT  wp_posts.post_title as name, 
address.meta_value as address,
 latitude.meta_value as lat,
 longitude.meta_value as lng,
 telephone.meta_value as telephone,

   ( 3959 * acos(
    cos( radians( '%s' ) ) *
    cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
    cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) - radians( '%s' ) ) +
    sin( radians( '%s' ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )
     ) ) AS distance

FROM wp_posts
LEFT JOIN wp_postmeta AS address ON(
wp_posts.ID = address.post_id
AND address.meta_key = '_dealer_address'
)
LEFT JOIN wp_postmeta AS latitude ON(
wp_posts.ID = latitude.post_id
AND latitude.meta_key = '_dealer_latitude'
)
LEFT JOIN wp_postmeta AS longitude ON(
wp_posts.ID = longitude.post_id
AND longitude.meta_key = '_dealer_longitude'
)
LEFT JOIN wp_postmeta AS telephone ON(
wp_posts.ID = telephone.post_id
AND telephone.meta_key = '_dealer_telephone'
)
WHERE wp_posts.post_type = 'dealers' HAVING distance < '%s' ORDER BY distance LIMIT 0 ,         20

Upvotes: 2

user556674
user556674

Reputation:

Try This:

    SELECT   wp_posts.post_title as name,          
 address.meta_value as address,       
  latitude.meta_value as lat,        
 longitude.meta_value as lng,      
   telephone.meta_value as phone,    
       ( 3959 * 
acos(         cos( radians( '%s' ) ) *     
    cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *    
   cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) - radians( '%s' ) ) + sin( radians( '%s' ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )          ) ) AS distance         
 FROM wp_postmeta as address,
 wp_postmeta as latitude,
 wp_postmeta as longitude, 
 wp_postmeta as telephone  left outer join    
      wp_posts on (wp_posts.ID = telephone.post_id    AND telephone.meta_key = '_dealer_telephone' ) 
WHERE        (wp_posts.ID = address.post_id     AND address.meta_key = '_dealer_address' )    
AND     (wp_posts.ID = latitude.post_id         AND latitude.meta_key = '_dealer_latitude' )  
AND     (wp_posts.ID = longitude.post_id         AND longitude.meta_key = '_dealer_longitude' )

Upvotes: 0

Dustin Laine
Dustin Laine

Reputation: 38553

For you final AND statement, simply add an OR condition for NULL's

AND     (wp_posts.ID = telephone.post_id
        AND telephone.meta_key IS NULL OR telephone.meta_key = '_dealer_telephone' )

Upvotes: 0

Related Questions