CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 11

Sql query to fetch multiple data from multiple tables

Given the following tables:

• Clients (ClientId, Name, Surname, Age)

• Products (ProductId, Name, Price)

• Purchases (Purchaseld, Date, ClientId, Productid)

I need to write an SQL query that shows the quantity of purchases made by clients. It must only show the clients who made more than 1 purchase. The result should contain the following fields: Full name (i.e. "john rambo"), Quantity of purchases.

I have written this query but results are not coming correct

SELECT Concat(clients.name, clients.surname) 
FROM   clients 
       JOIN products 
         ON clients.name = products.name 
       JOIN purchases 
         ON products.productid = purchases.productid 
GROUP  BY clientid 
HAVING Count(clientid) > 1

Upvotes: 1

Views: 1390

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

Your question has no mention of products, so there is no need for that in the query:

SELECT CONCAT(c.name, c.surname) 
FROM clients c JOIN
     purchases p
     ON p.ClientId = c.ClientId 
GROUP  BY c.ClientId, CONCAT(c.name, c.surname) 
HAVING COUNT(*) > 1 ;

Note that the ClientId presumably uniquely defines the clients -- not the names. So the ClientId should be part of the aggregation.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29619

SELECT Concat(clients.name, ' ', clients.surname),
       count(*) as number_of_orders
FROM   clients 
       JOIN purchases 
         ON products.productid = purchases.productid 
GROUP  BY Concat(clients.name, ' ', clients.surname) 
HAVING Count(clientid) > 1

As noted in the comments, your join to products doesn't make much sense - your asking to only return records where there's a product that matches a client's first name.

CONCAT will glue the two fields together (e.g. "JohnRambo")

Upvotes: 1

Related Questions