Getting SQL error while joining the multiple table using PHP and MySQL

Question

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

Incorrect usage of UNION and ORDER BY

Here is my query:

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

How can I resolve this error?

ScaisEdge · Accepted Answer · 2016-10-22 12:28:49Z

1

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

edited Oct 22, 2016 at 12:28

answered Oct 22, 2016 at 11:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

7 Comments

satya Over a year ago

I did as per you but its throwing this Unknown column 'b.member_id' in 'order clause' error again.

ScaisEdge Over a year ago

I have update the asnwer adding an alias cor b.member_id and using this alias i order by .. let me know

satya Over a year ago

its again throwing this ` #1052 - Column 'member_id' in order clause is ambiguous` error.

ScaisEdge Over a year ago

I have update the asnwer adding b_member_id and using the alias only

satya Over a year ago

again this ` #1054 - Unknown column 'b_member_id' in 'on clause'` error is coming.

|

Collectives™ on Stack Overflow

Getting SQL error while joining the multiple table using PHP and MySQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related