MYSQL getting data from three tables

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!

Answer

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;

Advertisement

Answer