MYSQL getting data from three tables

Question

I am trying to find the drinker and total amount of money spent on drinks for all drinkers in February 2020. I also need to include drinkers who haven't ordered a drink in this period.

Here are the three tables:

CREATE TABLE DRINKERS ( /* All drinkers */
DRINKER VARCHAR(30) NOT NULL,
    CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER)); 

CREATE TABLE SERVES(    /* Pubs serve drinks */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
PRICE       DECIMAL(5,2)    NOT NULL,   /* Drink price  */
    CONSTRAINT SERVES_PKEY PRIMARY KEY(PUB, DRINK),
        CONSTRAINT SERVES_FKEY1 FOREIGN KEY(PUB) 
        REFERENCES LOCATED(PUB),
    CONSTRAINT SERVES_FKEY2 FOREIGN KEY(DRINK)
        REFERENCES ALLDRINKS(DRINK)  );

CREATE TABLE ORDERS(    /* Drinkers visit pubs and consumes drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
ODATE       DATE        NOT NULL,   /* Order date   */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
DRINK_NO    DECIMAL(2)  NOT NULL,   /* A sequence number of a drink */
    CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK_NO),
    CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK) REFERENCES SERVES(PUB, DRINK),
    CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)   );

Here is my statement so far:

SELECT ORDERS.DRINKER, SUM(SERVES.PRICE)
FROM ORDERS
LEFT JOIN SERVES ON ORDERS.DRINKER = SERVES.PRICE
GROUP BY ORDERS.DRINKER;

I am very new to SQL, I know that there are a number of mistakes. Any help would be greatly appreciated!

ScaisEdge · Accepted Answer · 2020-05-15T14:24:33.303

1

You could use a join as

SELECT D.DRINKER , SUM(ifnull(S.PRICE,0))
FROM DRINKERS D
LEFT JOIN ORDERS O ON D.DRINKER  = O.DRINKER    
INNER JOIN  SERVES S ON O.PDB = S.PUB
    AND S.DRINK = O.DRINK
GROUP BY D.DRINKER;

and if you need only february

SELECT D.DRINKER , SUM(ifnull(S.PRICE,0))
FROM DRINKERS D
LEFT JOIN ORDERS O ON D.DRINKER  = O.DRINKER    
    AND year(O.ODATE) = 2020 AND month(O.ODATE) = 2
INNER JOIN  SERVES S ON O.PDB = S.PUB
    AND S.DRINK = O.DRINK

GROUP BY D.DRINKER;

edited May 15 '20 at 14:24

answered May 15 '20 at 13:30

ScaisEdge

131,976
10
91
107

we'd need an outer join to get drinkers who don't have an order ... – spencer7593 May 15 '20 at 13:57
Does that work? Your inner join on `serves` requires to be `orders` in the row, so your outer join becomes an inner join. You would have to make the `serves` join an outer join, too, to make the outer join work, if I am not mistaken. – Thorsten Kettner May 15 '20 at 14:10
@Noods I am confused. You explicitly mentioned that you need data for only February 2020 and this solution doesn't have `where` clause for that. – django-unchained May 15 '20 at 14:14

django-unchained · Answer 2 · 2020-05-15T13:55:59.853

You need to use simple inner join and left join along with month() and year() functions. You'll get result for all drinker regardless if they ordered a drink or not. So you'll have result for non-drinkers during February.

select a.drinker,sum(c.price)
from drinkers a left join orders b on a.drinker = b.drinker
join serves c on b.pub = c.pub and b.drink = c.drink
where month(b.odate) = 'February' and year(b.odate) = 2020
group by a.drinker;

Eric Brandt · Answer 3 · 2020-05-15T16:36:36.420

Since you want all DRINKERS, whether they've ordered or not, you'll want to start with that table. Then LEFT JOIN to the ORDERS table, to get the orders for those who have some.

This is a guess, but it appears from the column DRINK_NO that there is one row in ORDERS per drink served. Since you only want total drinks for this query, I would suggest using a sub-query of that table, rather than joining to the full table. Since this table includes the ODATE data, you can filter your results here in the sub-query. You could also filter with a WHERE clause on the outer query, but it will be more efficient doing it here.

SELECT
  DRINKER,
  PUB,
  DRINK,
  COUNT(DRINK_NO) AS QUANTITY 
FROM ORDERS
GROUP BY
  DRINKER,
  PUB,
  DRINK
WHERE
  MONTH(ODATE) = 2 AND YEAR(ODATE) = 2020

Finally, INNER JOIN your sub-query to SERVES in order to get the pricing information.

The fully constructed query should be close to this:

SELECT
  d.DRINKER,
  SUM(o.PRICE * o.QUANTITY) AS TotalSpent
FROM
  DRINKERS AS d
  LEFT JOIN
    (
      SELECT
        DRINKER,
        PUB,
        DRINK,
        COUNT(DRINK_NO) AS QUANTITY
      FROM ORDERS
      GROUP BY
        DRINKER,
        PUB,
        DRINK
      WHERE
        MONTH(ODATE) = 2 AND YEAR(ODATE) = 2020
    ) AS o
    ON o.DRINKER = d.DRINKER
  INNER JOIN SERVES AS s
    ON o.PUB = s.PUB
    AND o.DRINK = s.DRINK
GROUP BY
  d.DRINKER;

score 0 · Answer 4 · answered May 15 '20 at 14:11

There are a variety of query patterns that will return a result that satisfies the specification. Here's one example:

SELECT d.drinker               AS drinker
     , IFNULL(SUM(s.price),0)  AS tot_feb_prices
  FROM `DRINKERS` d
    -- 
  LEFT
  JOIN `ORDERS` o
    ON o.drinker  = p.drinker
   AND o.odate   >= '2020-02-01' 
   AND o.odate    < '2020-02-01' + INTERVAL 1 MONTH
    --
  LEFT
  JOIN `SERVES` s
    ON s.pub   = o.pub
   AND s.drink = o.drink
    --
 GROUP
    BY d.drinker
 ORDER
    BY d.drinker

another alternative is to use a correlated subquery in the select list (the subquery must return a scalar... only one column, and at most one row.)

SELECT d.drinker
    --
     , ( SELECT IFNULL(SUM(s.price),0)
           FROM `ORDERS` o
           JOIN `SERVES` s
             ON s.pub   = o.pub
            AND s.drink = o.drink
          WHERE o.drinker  = p.drinker
            AND o.odate   >= '2020-02-01' 
            AND o.odate    < '2020-02-01' + INTERVAL 1 MONTH
       ) AS tot_feb_prices
    --
  FROM `DRINKERS` d
 ORDER BY d.drinker

MYSQL getting data from three tables

4 Answers4