I need to find the distinct drinkers that ordered ‘VODKA’ and ‘WHISKY’. I AM ONLY ALLOWED TO USE A NESTED QUERY. No other format is accepted.
I am quite new to sql so any help would be greatly appreciated! Here are the tables I am trying to retrieve data from:
x
CREATE TABLE DRINKERS ( /* All drinkers */DRINKER VARCHAR(30) NOT NULL, CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER));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 SELECT statement so far:
SELECT DISTINCT DRINKERFROM DRINKERSWHERE EXISTS (SELECT DRINKER FROM ORDERS WHERE DRINKERS.DRINKER = ORDERS.DRINKER AND DRINK = 'VODKA' AND 'WHISKY');INSERT STATEMENTS:
INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('16-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('17-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 3);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 4);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 2);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 3);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('01-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('03-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('05-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'GREASY FORK', STR_TO_DATE('15-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('16-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('17-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('20-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('22-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-MAR-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAR-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('05-MAR-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-APR-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-MAY-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAY-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAY-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-JUN-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-JUN-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JUN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JUN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JUN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JUN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JUL-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JUL-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-AUG-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 5);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('11-JAN-2020','%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('PETER', 'LAZY LOBSTER', STR_TO_DATE('09-FEB-2020', '%d-%M-%Y'), 'PORT', 1);INSERT INTO ORDERS VALUES('PETER', 'GREASY FORK', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 3);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 4);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 5);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 6);INSERT INTO ORDERS VALUES('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 7);INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('MARY', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('MARY', 'CAPTAIN MOORE', STR_TO_DATE('03-MAR- 2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('MARY', 'LONG JOHN', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('MARY', 'GREASY FORK', STR_TO_DATE('24-APR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2);INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 3);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 3);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 4);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 5);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 6);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('JOHN', 'LONG JOHN', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('JOHN', 'CAPTAIN MOORE', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', STR_TO_DATE('10-JAN-2020','%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 1);INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 2);INSERT INTO ORDERS VALUES('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 3);INSERT INTO ORDERS VALUES('JAMES', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JAMES', 'LITTLE PIRATE', STR_TO_DATE('03- MAR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('JAMES', 'SWEET DREAMS', STR_TO_DATE('23-JUN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3);INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 2);INSERT INTO ORDERS VALUES('SERGIEY', 'GREASY FORK', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('23-FEB-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('09-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LITTLE PIRATE', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'VODKA', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 2);INSERT INTO ORDERS VALUES('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 3);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2);INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'GREASY FORK', STR_TO_DATE('20-APR-2020', '%d-%M-%Y'), 'BEER', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-APR- 2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('16-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('17-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1);INSERT INTO ORDERS VALUES('CLAUDE', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);DESIRED OUTPUT:
DRINKER SERGIEYAdvertisement
Answer
You could use an hanving for count distinct drink group by DRINKER
SELECT DRINKERS.DRINKERFROM DRINKERSINNER JOIN ORDERS DRINKERS.DRINKER = ORDERS.DRINKERWHERE ORDERS.DRINK IN ( 'VODKA' , 'WHISKY')GROUP BY DRINKERHAVING COUNT(DISTINCT ORDERS.DRINK ) = 2 or if you need a nested
SELECT DISTINCT DRINKER FROM DRINKERS WHERE EXISTS (SELECT DRINKER FROM ORDERS WHERE DRINK IN ( 'VODKA' AND 'WHISKY') GROUP BY DRINKER HAVING COUNT(DISTINCT ORDERS.DRINK ) = 2 );