satya satya - 1 month ago 7
MySQL Question

Getting SQL error while joining the multipletable using PHP and MySQL

I am getting the following error while trying to join multiple table using different condition in PHP and MySQL.


Error:


Incorrect usage of UNION and ORDER BY


I am explaining my query below.

select b.member_id,b.rest_name,b.city,b.proviance,b.postal,b.address,b.country,b.person,b.mobile,b.url,b.status,b.premium,b.image,b.business_phone_no,b.email,b.multiple_image,b.latitude,b.longitude,b.quadrant,d.member_id,d.day_id,d.cat_id,d.subcat_id,d.comment,d.city,d.special_images,c.cat_id,c.special,sub.subcat_id,sub.subcat_name,sub.status,sl.day_id,sl.member_id,sl.date_from,sl.date_to from db_restaurant_basic as b left join db_restaurant_detail as d on b.member_id=d.member_id left join db_category as c on d.cat_id=c.cat_id left join db_subcategory as sub on d.subcat_id=sub.subcat_id left join db_special_images as sl on d.day_id=sl.day_id and d.member_id=sl.member_id where b.city='2' and d.day_id='3' and c.special='2' and sl.date_from <='2016-10-26' and sl.date_to >= '2016-10-26' and b.status=1 and sub.status=1 and sl.date_from !='' and sl.date_to !='' ORDER BY b.member_id DESC UNION ALL SELECT b.member_id,b.rest_name,b.city,b.proviance,b.postal,b.address,b.country,b.person,b.mobile,b.url,b.status,b.premium,b.image,b.business_phone_no,b.email,b.multiple_image,b.latitude,b.longitude,b.quadrant,d.member_id,d.day_id,d.cat_id,d.subcat_id,d.comment,d.city,d.special_images,c.cat_id,c.special,sub.subcat_id,sub.subcat_name,sub.status,sl.day_id,sl.member_id,sl.date_from,sl.date_to from db_restaurant_basic as b left join db_restaurant_detail as d on b.member_id=d.member_id left join db_category as c on d.cat_id=c.cat_id left join db_subcategory as sub on d.subcat_id=sub.subcat_id left join db_special_images as sl on d.day_id=sl.day_id and d.member_id=sl.member_id where b.city='2' and d.day_id='3' and c.special='2' and b.status=1 and sub.status=1 and sl.date_from ='' and sl.date_to ='' ORDER BY b.member_id DESC


Please help me to resolve this error.

Answer

You should assign order by only an the end of the query not inside each select
and using proper alias for columns with same name in different table

select 
     b.member_id as b_member_id
    ,b.rest_name
    ,b.city
    ,b.proviance
    ,b.postal
    ,b.address
    ,b.country
    ,b.person
    ,b.mobile
    ,b.url
    ,b.status
    ,b.premium
    ,b.image
    ,b.business_phone_no
    ,b.email
    ,b.multiple_image
    ,b.latitude
    ,b.longitude
    ,b.quadrant
    ,d.member_id as d_member_id
    ,d.day_id
    ,d.cat_id
    ,d.subcat_id
    ,d.comment
    ,d.city
    ,d.special_images
    ,c.cat_id
    ,c.special
    ,sub.subcat_id
    ,sub.subcat_name
    ,sub.status
    ,sl.day_id
    ,sl.member_id
    ,sl.date_from
    ,sl.date_to 
from db_restaurant_basic as b 
left join db_restaurant_detail as d on b.b_member_id=d.d_member_id 
left join db_category as c on d.cat_id=c.cat_id 
left join db_subcategory as sub on d.subcat_id=sub.subcat_id 
left join db_special_images as sl on d.day_id=sl.day_id and d.member_id=sl.member_id 
where b.city='2' 
  and d.day_id='3' 
  and c.special='2' 
  and sl.date_from <='2016-10-26' 
  and sl.date_to >= '2016-10-26' 
  and b.status=1 
  and sub.status=1 
  and sl.date_from !='' 
  and sl.date_to !='' 

UNION ALL 

SELECT 
     b.member_id
    ,b.rest_name
    ,b.city
    ,b.proviance
    ,b.postal
    ,b.address
    ,b.country
    ,b.person
    ,b.mobile
    ,b.url
    ,b.status
    ,b.premium
    ,b.image
    ,b.business_phone_no
    ,b.email
    ,b.multiple_image
    ,b.latitude
    ,b.longitude
    ,b.quadrant
    ,d.member_id
    ,d.day_id
    ,d.cat_id
    ,d.subcat_id
    ,d.comment
    ,d.city
    ,d.special_images
    ,c.cat_id
    ,c.special
    ,sub.subcat_id
    ,sub.subcat_name
    ,sub.status
    ,sl.day_id
    ,sl.member_id
    ,sl.date_from
    ,sl.date_to 
from db_restaurant_basic as b 
left join db_restaurant_detail as d on b.member_id=d.member_id 
left join db_category as c on d.cat_id=c.cat_id 
left join db_subcategory as sub on d.subcat_id=sub.subcat_id 
left join db_special_images as sl on d.day_id=sl.day_id and d.member_id=sl.member_id 
where b.city='2' 
  and d.day_id='3' 
  and c.special='2' 
  and b.status=1 
  and sub.status=1 a
  nd sl.date_from ='' 
  and sl.date_to ='' 
ORDER BY b_member_id DESC
Comments