CODEWITHSUNDEEP
mysqlsqljoin
alim1990
alim1990

Reputation: 4972

MySQL query error when joining 3 tables

I have the following 3 table:

enter image description here

And I need to set an HTML table where it displays the following:

        <tr class="bg-info">
          <th>Med ID</th>
          <th>Med Name</th>
          <th>Med Expiry</th>
          <th>Barcode</th>
          <th>Number of Tablets received</th>
          <th>Total Number of Pills received</th>
          <th>Date Received</th>
          <th>Pills distributed</th>
          <th>Still (in tablets)</th>
          <th>Still (in pills)</th>
        </tr>

So I created this SQL Query:

select t1.med_id,
t3.med_name,
t1.med_expiry, 
t1.med_barcode, 
t1.med_tablet, 
t1.med_pill, 
t1.med_received,
sum(t2.given_quantity)
FROM med_pharmacy t1, consultation_med t2, medication t3 WHERE t1.med_pharmacy_id = t2.med_pharmacy_id AND t1.med_id=t3.med_id
AND t1.clinic_id='361'

And I received the following error:

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'ncd.t1.med_id'; this is incompatible with sql_mode=only_full_group_by

Upvotes: 0

Views: 68

Answers (1)

fancyPants
fancyPants

Reputation: 51868

You're using GROUP BY wrong. The rule is that each column in your SELECT clause is either also in your GROUP BY clause or an aggregate function (like count, min, max, avg) must be applied to it.

The sql_mode ONLY_FULL_GROUP_BY prevents that. What happens when you disable it, can be seen in this question: Why i get Null values in my second counter using case statement

The solution can also be found in the linked question. To repeat it here: apply aggregate functions or include the columns in the group by clause.

You fix it either like this:

select t1.med_id,
t3.med_name,
t1.med_expiry, 
t1.med_barcode, 
t1.med_tablet, 
t1.med_pill, 
t1.med_received,
sum(t2.given_quantity)
FROM med_pharmacy t1, consultation_med t2, medication t3 WHERE t1.med_pharmacy_id = t2.med_pharmacy_id AND t1.med_id=t3.med_id
AND t1.clinic_id='361'
group by 
t1.med_id,
t3.med_name,
t1.med_expiry, 
t1.med_barcode, 
t1.med_tablet, 
t1.med_pill, 
t1.med_received

or like this

select 
MAX(t1.med_id),
MAX(t3.med_name),
MAX(t1.med_expiry), 
MAX(t1.med_barcode), 
MAX(t1.med_tablet), 
MAX(t1.med_pill), 
MAX(t1.med_received),
sum(t2.given_quantity)
FROM med_pharmacy t1, consultation_med t2, medication t3 WHERE t1.med_pharmacy_id = t2.med_pharmacy_id AND t1.med_id=t3.med_id
AND t1.clinic_id='361'

or a combination of both, depending on your needs.

When you're using MySQL 5.7 there's also the new function any_value() which you can use instead of an aggregate function. But like the name suggests, it returns any value of the group.

Upvotes: 2

Related Questions