How to union 2 select without duplication

You had the right idea. But as others have stated, GROUP BY is your best friend here. Also, make use of DISTINCT to get rid of counting a waiter twice for the same order. This is how your code should look like

// An inner select query whose purpose is to count all waiter per room// The key here is to group them by `oid` since we are interested in the order// Also, in the count(), use DISTINCT to avoid counting duplicates$this->db->select('room.oid, count(DISTINCT room.waiter_assigned) AS total_waiters');$this->db->from('room');$this->db->group_by('room.oid');$query1 = $this->db->get_compiled_select();// If you run $this->db->query($query1)->result(); you should seeoid |  total_waiters----+-------------------------------1   |      12   |      13   |      2// This is how you would use this table query in a join.// LEFT JOIN also considers those rooms without waiters// IFNULL() ensures that you get a 0 instead of null for rooms that have no waiters$this->db->select('order.oid, order.name, IFNULL(joinTable.total_waiters, 0) AS total_waiters');$this->db->from('order');$this->db->join('('.$query1.') joinTable', 'joinTable.oid = order.oid', 'left');$this->db->get()->result();// you should seeoid |  name     |  total_waiters----+-----------+-------------------------1   |  aa       |      12   |  bb       |      13   |  cc       |      24   |  dd       |      0

Here is the raw SQL statement

SELECT order.oid, order.name, IFNULL(joinTable.total_waiters, 0) AS total_waitersFROM orderLEFT JOIN (    SELECT room.oid, count(DISTINCT room.waiter_assigned) AS total_waiters      FROM room    GROUP BY room.oid) joinTable ON joinTable.oid = order.oid