Sql query to fetch multiple data from multiple tables

Question

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

Neville Kuyt · Accepted Answer · 2020-08-10 09:46:06Z

1

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")

edited Aug 10, 2020 at 9:46

answered Aug 10, 2020 at 9:40

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

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

Gordon Linoff · Accepted Answer · 2020-08-10 10:36:45Z

0

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.

answered Aug 10, 2020 at 10:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Sql query to fetch multiple data from multiple tables

2 Answers 2

1 Comment

Comments

Your Answer

Collectives™ on Stack Overflow

Sql query to fetch multiple data from multiple tables

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest