SQL Queries

[JakenHerman]

For favorites : (SHOW) 1) Get UserID from Code 2) SELECT DISTINCT V.VenueName FROM Boozehound.Venues As V, Boozehound.Favorites AS F Where f.venueid = v.venueid and f.userid = ? //user id specified by code from login (ADD) INSERT INTO Boozehound.favorites (userid, venueid) values (?, ?);

For Users: If { SELECT * FROM Boozehound.users WHERE username = ? } else { INSERT INTO Boozehound.users (username) values (?) }

For Venue Page ADDRESS: SELECT A.Street, A.City, A.State, A.Zip, V.Phone FROM Boozehound.Address AS A, Boozehound.venues AS V WHERE v.venueid = a.venueid

OPEN HOURS: SELECT V.VenueName, H.Start_Time, H.End_Time FROM Boozehound.venues as v, boozehound.hours as h WHERE h.happyhourid =?

//0#### = Time Code (i.e. 0 = operating hours, 1 = first happy hour, 2 = second happy hour, 3 = all day, 4 = no hphr //#0### = Day Code ( 0= sunday, 6 = saturday) //###00 = VenueID //code will need to figure out which happy hour is going on

HAPPY HOURS SELECT v.venuename, h.start_time, h.end_time from Boozehound.venues as v, boozehound.hours as h where h.happyhourid=?

selct v.venuename, o.price from boozehound.offers as o, boozehound.hours as h boozehound.venues as v where h.happyhourid = ? and v.venueid =?

[JakenHerman]

For favorites : (DELETE) Delete from boozehound.favorites where userid = ? and venueid = ?

[DomRegalado]

New Open Hours SELECT H.HappyHourID, H.Day, V.VenueName, H.Start_Time, H.End_Time FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID where H.VenueID in ('?') AND SUBSTRING(H.HappyHourID, 1, 1) in ('?');

//Specify the 5-digit VenueID 000** for the first '?' and the single digit 0-4 operating digit for the second '?'

[DomRegalado]

For every Venue Page's Happy Hour Offers for Each day: SELECT H.HappyHourID, V.VenueName, O.Price FROM Boozehound.Offers AS O INNER JOIN Boozehound.Hours AS H ON O.HappyHourID=H.HappyHourID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID where H.VenueID in ('?') and H.Day = '?'; // 5-digit VenueID and 1-digit Day Code from 0-6

[DomRegalado]

For Phone Numbers: SELECT V.Phone FROM Boozehound.Venues AS V WHERE V.VenueID='?'; //5 digit VenueID

[DomRegalado]

For Filters on Main Page: Drink filters 'Mixed' 'Beer' 'Margarita' 'Liquor' 'Bomb' 'Shot' Switch case for 1 drink filter SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code Switch case for 2 drink filters SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?', '?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code Switch case for 3 drink filters SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?', '?', '?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code Switch case for 4 drink filters SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?', '?', '?', '?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code Switch case for 5 drink filters SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?','?', '?', '?', '?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code Switch case for 6 drink filters SELECT V.VenueName, V.VenueID FROM Boozehound.Address AS A INNER JOIN Boozehound.Hours AS H ON A.VenueID=H.VenueID INNER JOIN Boozehound.Venues AS V ON V.VenueID=H.VenueID INNER JOIN Boozehound.Offers AS O ON H.HappyHourID=O.HappyHourID INNER JOIN Boozehound.Drinks AS D ON O.DrinkID=D.DrinkID where D.DrinkType in ('?','?','?', '?', '?', '?') and SUBSTRING(O.HappyHourID, 2, 1) in ('?') //Specify day code