Mysql – Select data with a join

MySQL

I just started learning SQL now and trying to figure out this scenario:

We have 3 tables:

Clients (ClientID, Name, Surname, Age)
Products (ProductID, Name, Price)
Purchases (PurchaseID, Date, ClientID, ProductID)

What would be the best SQL query that will show the quantity of purchases (how many products they bought) made by clients?

It must only show the clients who made more than 1 purchase.

The result should contain the following fields:
Full name, Quantity of purchases.

Best Answer

You could do it like this:

SELECT  CONCAT(Name,' ', Surname) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C 
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;

DB-Fiddle

As a remark, if Fullname or Surname can be NULL then you would have to add a check for that, could be done with IFNULL()

SELECT  CONCAT(IFNULL(Name,''),' ', IFNULL(Surname,'')) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C 
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;

Related Solutions

Mysql – How to profile a particularly slow or inefficient query

You didn't provide CREATE TABLE scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....

If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE), or column[s] you have in GROUP BY or ORDER BY. In general, a good sign that some indexes are missed is when EXPLAIN shows "NULL" in key,key_len and "ALL" in type column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup). Then you may want to tweak some indexes to make them covering for this query.

Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).

MySQL select with left join query

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname, 
   class.hours as hours, 
   teachers.first_name as teacherFirstName, 
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls 
 left join class on class.id = cls.class_ref  
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

Best Answer

Related Solutions

Mysql – How to profile a particularly slow or inefficient query

MySQL select with left join query

Related Question