How to use sum, join, order by and where clause in mysql?

Question

So I have two tables like given below:

1. tb_custmap: idCust, idUser
2. tb_cust: idCust, revenue

I want to get the revenue from tb_cust based on the idCust and only from specific idUser. I've tried this:

SELECT tb_cust.revenue
FROM tb_custmap INNER JOIN
     tb_cust
     ON tb_custmap.idCust = tb_cust.idCust
ORDER BY tb_cust.revenue
WHERE idUser='".$AccMgrID."'

But I got error and it is said

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE idUser='azkyath'' at line 1 "

Please someone help me ive been doing this for 2 days and still can't get the right one.

Answer 1

Hope this will help you :

Do like this with the use of CI query builder

/*if you want to use `sum` use this instead of select
 $this->db->select_sum('tb_cust.revenue');
*/
$this->db->select('tb_cust.revenue');
$this->db->from('tb_custmap');
$this->db->join('tb_cust' , 'tb_cust.idCust = tb_custmap.idCust');

/*In where clause replace `table` name also with the correct table name 
 from where `idUser` column belongs to
*/
$this->db->where('table.idUser', $AccMgrID);

$this->db->order_by('tb_cust.revenue','ASC');
$query = $this->db->get();
if ( $query->num_rows() > 0 )
{
    print_r($query->result());
}

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#selecting-data

Answer 2

You should write the query like this:

SELECT c.revenue
FROM tb_custmap cm INNER JOIN
     tb_cust c
     ON cm.idCust = c.idCust
WHERE ?.idUser = :AccMgrID
ORDER BY c.revenue;

Notes:

• WHERE goes after FROM and before ORDER BY.
• Use table aliases that are the abbreviation of the table name.
• Qualify all column names (i.e., use the table alias).
• The ? is for the table where idUser comes from.
• :AccMgrID is for a parameter. You should learn how to use parameters if you are going to use SQL effectively from a programming language.