Hemant Jaiswal Hemant Jaiswal - 22 days ago 8
MySQL Question

Getting issue in union of 2 queries

I am getting an issue in below query -

SELECT DISTINCT id
FROM wp_posts
INNER JOIN wp_postcodes ON (
wp_postcodes.post_id = wp_posts.id
AND wp_posts.post_type = 'listing'
AND TRUNCATE (
(degrees(acos(sin(radians(latitude)) * sin(radians('37.090240')) + cos(radians(latitude)) * cos(radians('37.090240')) * cos(radians(longitude - '-95.712891')))) * 69.09),
1
) <= 400
AND TRUNCATE (
(degrees(acos(sin(radians(latitude)) * sin(radians('37.090240')) + cos(radians(latitude)) * cos(radians('37.090240')) * cos(radians(longitude - '-95.712891')))) * 69.09),
1
) >= 40
)
INNER JOIN wp_postmeta ON (
wp_postmeta.post_id = wp_posts.id
AND wp_posts.post_type = 'listing'
AND wp_posts.post_status = 'publish'
AND (
(
meta_key = 'city'
AND meta_value = 'sports'
)
OR (
meta_key = 'state'
AND meta_value = 'sports'
)
OR post_title = 'sports'
)
)
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_terms ON (wp_terms.term_id = wp_term_relationships.term_taxonomy_id)
INNER JOIN wp_term_taxonomy ON (
wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'listingcategory'
AND wp_term_taxonomy.term_id = 406
)

UNION

DISTINCT
SELECT DISTINCT id
FROM wp_posts
INNER JOIN wp_postcodes ON (
wp_postcodes.post_id = wp_posts.id
AND wp_posts.post_type = 'listing'
AND TRUNCATE (
(degrees(acos(sin(radians(latitude)) * sin(radians('37.090240')) + cos(radians(latitude)) * cos(radians('37.090240')) * cos(radians(longitude - '-95.712891')))) * 69.09),
1
) <= 400
AND TRUNCATE (
(degrees(acos(sin(radians(latitude)) * sin(radians('37.090240')) + cos(radians(latitude)) * cos(radians('37.090240')) * cos(radians(longitude - '-95.712891')))) * 69.09),
1
) >= 40
)
INNER JOIN wp_postmeta ON (
wp_postmeta.post_id = wp_posts.id
AND wp_posts.post_type = 'listing'
AND wp_posts.post_status = 'publish'
AND (
(
meta_key = 'city'
AND meta_value LIKE '%sports%'
)
OR (
meta_key = 'state'
AND meta_value LIKE '%sports%'
)
OR post_title LIKE '%sports%'
)
)
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_terms ON (wp_terms.term_id = wp_term_relationships.term_taxonomy_id)
INNER JOIN wp_term_taxonomy ON (
wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'listingcategory'
AND wp_term_taxonomy.term_id = 406
)


here it have 2 queries with union, 1st getting no output but 2nd getting output. And I am performing union of both queries. here should be a result
but I am getting no result. Please suggest what mistake I am doing..

Answer

Thanks for reply. I have resolved my issue, here UNION is working fine for me instead of UNION DISTINCT. Now my concern has been solved.