CODEWITHSUNDEEP
mysqlsqlleft-joinunion
Alex van Rijs
Alex van Rijs

Reputation: 813

Merging and Summing data from SQL Database

So I am trying to make a system faster. Currently I am polling the database several thousand times for one transation. I am in the process of making this faster by combining datasets/tables. Currently I've got 4 tables in my database: Customers, Food Transactions, Drink Transactions and Desert Transactions. (These are placeholder names). These are set up as followed: Each entering customer gets a unique transaction number that will be used to order stuff

**Customers Table:**
ID              Customer Name
0               Harvey
1               Jessica
2               Rachel
3               Louis
4               Mike
5               Donna

**Food Table**
Transaction ID  Food ID    Customer ID
1121            4          1
1122            2          0
1125            9          3
1122            7          0
1120            6          2
1122            6          0

**Drinks Table:**
Transaction ID  Drink ID   Customer ID
1121            2          1
1121            4          1
1121            4          1
1120            3          2
1125            1          3
1130            8          4
1132            8          4

**Desert Table:**
Transaction ID  Desert ID  Customer ID
1130            1          4
1125            3          3
1120            3          2
2100            9          5
1118            8          5
1118            7          5

Now I have been trying to combine these tables with FULL OUTER JOIN, I however think that I might have mis interprated this function. I was able to find the corresponding customer name by using LEFT JOIN.

Does anybody have any clue, how I could create the rest of the following table:

**Combined Table**
Transaction IDs     Customer Name       Amount Foods     Amount Drinks     Amount Dessert
2100                Donna               0                0                 1
1132                Mike                0                1                 0
1130                Mike                0                1                 1
1125                Louis               1                1                 1
1122                Harvey              3                0                 0
1121                Jessica             1                3                 0
1120                Rachel              1                1                 1
1118                Donna               0                0                 2

A Transaction belongs to one customer, a customer however can have several transaction ID's. Therefore, since the Transaction ID's are unique in the last table I have no idea what you would use as the left table when using a LEFT JOIN statement.

Upvotes: 0

Views: 78

Answers (2)

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

maybe this query?

SELECT `Food`.`Customer ID`, `Customers`.`Customer Name`,
   COUNT(DISTINCT `Food`.`Food ID`) as "Amount Foods",
   COUNT(DISTINCT `drink`.`Food ID`) as "Amount drinks",
   COUNT(DISTINCT `desert`.`Food ID`) as "Amount deserts"
FROM `Food` 
LEFT OUTER JOIN `Customers` ON `food`.`Customer ID` = `Customers`.`ID` 
LEFT OUTER JOIN `Customers` ON `drink`.`Customer ID` = `Customers`.`ID` 
LEFT OUTER JOIN `Customers` ON `desert`.`Customer ID` = `Customers`.`ID` 
GROUP BY `Food`.`Customer ID`, `Customers`.`Customer Name`;

EDIT:

I will suggest to change table structure if this is possible.

  • Table 1: Customer (CustomerID, customer_name,...)
  • Table 2: Transaction (TransactionID, CustomerID, maybe date, time,...)
  • Table 3: Order (OrderID, TransactionID, type (1 - food, 2 - Drink, 3 - desert), date, time,...)

Then you can write query like this

SELECT t.transactionid, c.customername,
    (SELECT count(*) FROM order food WHERE  t.transactionid = food.transactionid and food.type = 1) as food_count,
    (SELECT count(*) FROM order drink WHERE  t.transactionid = drink.transactionid and drink.type = 2) as drink_count,
    (SELECT count(*) FROM order desert WHERE  t.transactionid = desert.transactionid and desert.type = 3) as desert_count
FROM transaction t
INNER JOIN customer c on t.customerid = c.customerid
GROUP BY t.transactionid, c.customername;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Here is another way of writing your query. It uses correlated subqueries instead of joins in the outer query:

SELECT c.`Customer ID`, c.`Customer Name`,
       (select COUNT(DISTINCT f.`Food ID`) from Food f where f.`Customer ID` = c.`ID`
       ) as "Amount Foods",
       (select COUNT(DISTINCT d.`Drink ID`) from Drink d where d.`Customer ID` = c.`ID`
       ) as "Amount Drinks",
       (select COUNT(DISTINCT d.`Desert ID`) from Desert d where d.`Customer ID` = c.`ID`
       ) as "Amount Desert",
FROM Customers c ;

This does a few things:

  • It prevents cartesian products at the outer level, as you add more tables.
  • It eliminates the need for a group by in the outer query.
  • It ensures that all customers are present, even if they have no rows in any of the tables.

Note: I kept the count(distinct). However, you probably do not need it. count(*) should be sufficient, unless you know there are duplicate values that you do not want to count.

Upvotes: 2

Related Questions