CODEWITHSUNDEEP
mysql
user3325207
user3325207

Reputation: 59

Mysql complex select query from 4 tables

I research almost 200 example pages about mysql complex queries but stuck in it.

This is my stucture

Table name: zones

zoneId | zoneName
------------------

Table name: customers

customesId | zoneId | customerName
----------------------------------

Table name: products

productId | productName
-----------------------

Table name: sales

sid | zoneId | customerId | productId | amount
----------------------------------------------

Is it possible to get the following output only with the query?

zoneName | customerName | productName   | amount(SUM)
---------------------------------------------------
ZoneX    | customerA    | productName_1 | 10
         |              | productName_2 | 0
         |              | productName_3 | 4
         |              | productName_4 | 0
ZoneX    | customerB    | productName_1 | 7
         |              | productName_2 | 0
         |              | productName_3 | 4
         |              | productName_4 | 3
.......

I want to get as "0" even customer or product has no sale

I tried:

SELECT zones.zoneName
     , customers.customerName
     , products.productName
     , SUM(amount) AS amount 
FROM customers 
INNER JOIN zones 
  ON customers.zoneId = zones.zoneId 
LEFT JOIN sales 
  ON customers.customerId = sales.customerId 
LEFT JOIN products 
  ON sales.productId = products.productId

Upvotes: 2

Views: 93

Answers (2)

xQbert
xQbert

Reputation: 35323

You need to cross join all the customers to the products so that each customer has every product listed regardless of sale. 

SELECT z.zoneName
     , c.customerName
     , p.productName
     , SUM(coalesce(s.amount,0)) AS amount 
FROM customers c
INNER JOIN zones z 
  ON c.zoneId = z.zoneId 
CROSS JOIN PRODUCTS P
LEFT JOIN sales S
  ON c.customerId = s.customerId
 and s.productID = p.productID 
GROUP BY z.zoneName
       , c.customerName
       , p.productName

Upvotes: 2

Ronak Patel
Ronak Patel

Reputation: 671

You can try this query 

SELECT c.zoneId ,c.customesId ,c.customerName,IF(s.amount IS NULL, 0 , s.amount) 
FROM customers AS c, products AS p
LEFT JOIN sales AS s ON s.productId  = p.productId  and s.customersid = c.customersid

Hope this helps.

Upvotes: 0

Related Questions