suresh kannan suresh kannan - 1 month ago 8
MySQL Question

Error Number: 1241 Operand should contain 1 column(s)

SELECT *
FROM `tbl_equipments` as `E`
JOIN `tbl_equipment_category` as `C` ON `C`.`equipment_category_id`=`E`.`equipment_category_id`
JOIN `tbl_suppliers` as `S` ON `S`.`supplier_id`=`E`.`supplier_id`
JOIN `tbl_site_users` as `U` ON `U`.`user_id`=`E`.`created_by`
JOIN `tbl_currency` as `R` ON `R`.`currency_id`=`E`.`equipment_currency_id`
WHERE `E`.`site_id` = '3'
AND (SELECT *
FROM `tbl_user_delegates` as `D`
LEFT JOIN `tbl_equipments` as `E` ON `E`.`approve_by`=`D`.`delegate_from_user`
WHERE `D`.`delegate_from_date` <= '2016-10-27'
AND `D`.`delegate_to_date` >= '2016-10-27' AND `D`.`delegate_to_user` = '5'
ORDER BY `E`.`equipment_id` DESC)
AND (`E`.`approve_flg` =0 and `E`.`rejection_flg` =0) AND `E`.`approve_by` = '5'

Answer

Your AND ( subselect ) return * this is wrong

you should use exists

  SELECT * 
  FROM `tbl_equipments` as `E` 
  JOIN `tbl_equipment_category` as `C` ON `C`.`equipment_category_id`=`E`.`equipment_category_id` 
  JOIN `tbl_suppliers` as `S` ON `S`.`supplier_id`=`E`.`supplier_id` 
  JOIN `tbl_site_users` as `U` ON `U`.`user_id`=`E`.`created_by` 
  JOIN `tbl_currency` as `R` ON `R`.`currency_id`=`E`.`equipment_currency_id` 
  WHERE `E`.`site_id` = '3' 
  AND  EXISTS (SELECT * 
        FROM `tbl_user_delegates` as `D` 
        LEFT JOIN `tbl_equipments` as `E` ON `E`.`approve_by`=`D`.`delegate_from_user` 
        WHERE `D`.`delegate_from_date` <= '2016-10-27' 
        AND `D`.`delegate_to_date` >= '2016-10-27' AND `D`.`delegate_to_user` = '5' 
        ORDER BY `E`.`equipment_id` DESC) 
  AND (`E`.`approve_flg` =0 and `E`.`rejection_flg` =0) AND `E`.`approve_by` = '5'

adn Just a tips order by in select select is unuseful

Comments