Query - Samples

Since the tags aren't as obvious using query, you can always assign the AS statement ie: storyText as story.content

Stories Samples


Brexit Stories	All Stories that include mention of Brexit	SELECT storyText, header FROM story WHERE storyText like '%brexit%'
Hilary Clinton Stories	All Stories that include mention of Clinton	SELECT storyText, header FROM story WHERE storyText like '%clinton%'
AP Stories	All AP Topics	SELECT S.storyText AS story, S.header AS headline, T.name as topic FROM story S JOIN story_topic T ON S.topicID=T.ID WHERE T.name IN ('AP Business', 'AP Entertainment', 'AP Politics', 'AP Top Headlines') AND S.output=1 ORDER BY topic
Stories - First Sentence Only	Stories and include only the first sentence.	SELECT IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText))) > 0, SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText)), S.storyText) AS header, T.broadcastname FROM story S JOIN story_topic T ON S.topicID=T.ID WHERE T.name=@Topic
Stories - Single Paragraph Only	First paragraph only of each story	SELECT IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText))) > 0, SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText)), S.storyText) AS header, T.broadcastName FROM story S JOIN story_topic T ON S.topicID=T.ID WHERE T.name=@Topic
Stories from a specific topic	Stories from a specific topic using parameters	SELECT S.storyText, S.header, T.broadcastName FROM story S, story_topic T WHERE S.topicid=T.ID AND T.name LIKE @Topic AND S.output=1 /Example BBC% / ORDER BY T.broadcastName, S.priority DESC

Sports Samples


Final MLB Scores	All MLB Scores that are final	SELECT L.name as league, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore as vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore as hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final' AND L.name = 'MLB'
Final Scores	All scores that have gone final	SELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final'
New York Games	Games that include NYC teams	SELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND (V.location = 'New York' OR H.location = 'New York')
Scores - NHL Top 10 Players	Scores - NHL Top 10 Players w params	SELECT P.firstname, P.lastname, T.code, T.location, T.name, 'Top 10 Assists' as topic, MAX(case when TAG.key = @stat then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as stat, /A example for stat/ MAX(case when TAG.key = 'headshot' then TAG.`value` end) as headshot FROM teamplayer_tag TAG JOIN team_player P on TAG.teamplayerID=P.ID JOIN team T on P.teamID=T.ID JOIN league L on L.ID=T.leagueID WHERE L.code='NHL' GROUP BY TAG.teamplayerID ORDER BY stat DESC, P.lastname, P.firstname LIMIT 10
Scores - NBA Standings Divisions	Scores - NBA Standings w Params	SELECT MAX(case when TAG.key = 'divisionRank' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as ranks, T.code, T.location, T.name, MAX(case when TAG.key = 'division' then TAG.`value` end) as division, MAX(case when TAG.key = 'GP' then TAG.`value` end) as GP, MAX(case when TAG.key = 'W' then TAG.`value` end) as W, MAX(case when TAG.key = 'L' then TAG.`value` end) as L, MAX(case when TAG.key = 'PCT' then TAG.`value` end) as PCT, MAX(case when TAG.key = 'divisionGB' then TAG.`value` end) as GB, MAX(case when TAG.key = 'home' then TAG.`value` end) as home, MAX(case when TAG.key = 'road' then TAG.`value` end) as away, MAX(case when TAG.key = 'last10' then TAG.`value` end) as L10, MAX(case when TAG.key = 'streak' then TAG.`value` end) as STRK FROM team_tag TAG JOIN team T ON TAG.teamID=T.ID JOIN league L ON T.leagueID=L.ID WHERE L.code='NBA' GROUP BY TAG.teamID HAVING division=@division ORDER BY ranks

Custom Samples


Golf Query	Golf Query using parameters	SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'; SELECT P.* FROM (SELECT `value` AS Y FROM custom_tag WHERE `key`=@prop AND customID= (SELECT C.ID FROM custom_tag TAG JOIN custom C ON TAG.customID=C.ID JOIN custom_topic T ON C.topicID=T.ID WHERE `key`= 'name' AND `value`=@event AND T.name='Golf Sportsbook')) AS PROP, JSON_TABLE( CAST(PROP.Y AS JSON), '$[*]' COLUMNS( name VARCHAR(100) PATH '$.name', odds VARCHAR(100) PATH '$.odds' ) ) AS P;
Go Transit Buses	Go Transit Buses	SELECT 'Go Transit Buses' as service, MAX(case when TAG.key = 'zone' then TAG.`value` end) as zone, MAX(case when TAG.key = 'stop' then TAG.`value` end) as stop, MAX(case when TAG.key = 'routeID' then TAG.`value` end) as routeID, MAX(case when TAG.key = 'route' then TAG.`value` end) as route, MAX(case when TAG.key = 'routeColour' then TAG.`value` end) as routeColour, MAX(case when TAG.key = 'routeTextColour' then TAG.`value` end) as routeTextColour, MAX(case when TAG.key = 'routeType' then TAG.`value` end) as routeType, MAX(case when TAG.key = 'direction1' then TAG.`value` end) as destination1, MAX(case when TAG.key = 'departureTime1' then TAG.`value` end) as departureTime1, MAX(case when TAG.key = 'direction2' then TAG.`value` end) as destination2, MAX(case when TAG.key = 'departureTime2' then TAG.`value` end) as departureTime2, MAX(case when TAG.key = 'direction3' then TAG.`value` end) as destination3, MAX(case when TAG.key = 'departureTime3' then TAG.`value` end) as departureTime3 FROM custom_tag TAG JOIN custom C on TAG.customID=C.ID JOIN custom_topic T on C.topicID=T.ID WHERE T.name='Scotia Bank Arena Public Transit' GROUP BY TAG.customID HAVING routeType='bus' AND zone='Toronto_GO' AND destination1 NOT LIKE '%union station%' ORDER BY route
US Elections Trend Reports	Custom - US Elections Trend Reports w params	SELECT 218 AS majority, partyChip, total, CASE WHEN X.p='DEM' THEN 'Democratic' WHEN X.p<>'DEM' THEN 'Republican' END AS party FROM (SELECT MAX(case when TAG.key = 'party' then TAG.`value` end) as p, MAX(case when TAG.key = 'party' then CONCAT(TAG.`value`, '.png') end) as partyChip, MAX(case when TAG.key = 'Winning Trend' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as total FROM custom_tag TAG JOIN custom C on TAG.customID=C.ID JOIN custom_topic T on C.topicID=T.ID WHERE T.name=@race /House Trend Report Example/ GROUP BY TAG.customID LIMIT 2) X ORDER BY total DESC

Elections Samples

Elections - California Proposition 68

SELECT CANDIDATE.lastname AS choice, CHOICE.votes
FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
JOIN elc_office OFFICE ON CONTEST.officeID=OFFICE.ID
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_candidate CANDIDATE ON choice.candidateID=CANDIDATE.ID
WHERE OFFICE.name = 'Proposition 68-Fund Outdoor Access' AND EVENT.name='California State Primary 2018'
ORDER BY CHOICE.votes DESC

Elections - Party Totals

Elections - Party Totals params

SELECT * FROM
((SELECT nameShort AS partyCode, name AS partyName, chicklet, COUNT(*) as total
FROM (
SELECT * FROM
(SELECT PARTY.name, PARTY.nameShort, CHOICE.votes, CHOICE.contestID, PARTY.color AS chicklet
FROM elc_candidate CANDIDATE
JOIN elc_party PARTY ON CANDIDATE.partyID=PARTY.ID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
WHERE CHOICE.contestID IN
(SELECT CONTEST.ID FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event)
ORDER BY contestID, votes DESC) x
GROUP BY contestID) y
GROUP BY nameShort)
UNION
(SELECT distinct P.nameShort AS partyCode, P.name AS partyName, P.color AS chicklet, 0 AS total
FROM elc_party P
JOIN elc_candidate CANDIDATE ON P.ID=CANDIDATE.partyID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
JOIN elc_contest CONTEST ON CHOICE.contestID=CONTEST.ID
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event

/*Alberta 2019*/

AND P.nameShort IN ('UCP', 'NDP', 'AP', 'LIB', 'GRN', 'WRP'))) z
GROUP BY partyCode
ORDER BY total DESC, partyCode

Elections - Electoral College Total

SELECT CHOICE.votes, p.name
FROM elc_contest CONTEST
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_event E ON E.ID=CONTEST.eventID
JOIN elc_office O ON CONTEST.officeID=O.ID
JOIN elc_candidate CANDIDATE ON CHOICE.candidateID=CANDIDATE.ID
JOIN elc_party P ON CANDIDATE.partyID=P.ID
WHERE E.name='US and Statewide Popular and Electoral Votes 2016'
AND O.name='Electoral College'
AND P.nameShort IN ('REP', 'DEM')

Finance Samples


Highest Volume Stocks	The 5 highest volume stocks	SELECT name, symbol, volume FROM stock ORDER BY volume DESC limit 5
Top Gaining Stocks	The 5 top gaining stocks by percentage	SELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price DESC limit 5
Top Losing Stocks	The 5 top losing stocks by percentage	SELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price ASC limit 5
Market Volume Leaders	Market Volume Leaders using parameters	SELECT symbol, name, volume FROM stock S JOIN stock_topic T ON S.topicID=T.ID WHERE T.market=@market /Dow Jones 30 Example/ ORDER by S.volume DESC LIMIT 10

JSON Samples

Supported in mySQL version 8.x

JSON extract from a JSON dynamic tag

pull a json feed: @prop

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SET @PROP := 'o_Grid_Position_of_Winner';
SET @LEAGUE := 'F1';
SELECT P.* FROM
(SELECT Y FROM
(SELECT JSON_EXTRACT(X.json, '$.runners') AS Y FROM
(SELECT TAG.`value` AS json FROM league_tag TAG JOIN league L ON TAG.leagueID=L.ID WHERE `key`= @PROP AND L.code=@LEAGUE) AS X) AS Z) AS PROP,
JSON_TABLE(
CAST(PROP.Y AS JSON),
'$[*]' COLUMNS(
name VARCHAR(100) PATH '$.name',
odds VARCHAR(100) PATH '$.odds',
active BOOL PATH '$.active'
)
) AS P;

Tweet Samples


Tweets with Trump		SELECT `tweet`, `fromAccount`, `fromName`, `avatarUrl` FROM tweet WHERE `tweet` like '%trump%'

Weather Samples


Hottest Cities	The 5 hottest cities	SELECT C.cityName as name, D.temp as temp, CONCAT('file:///D:/',W.`filename`) as media FROM weather W, weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id AND w.`ID` = D.`weatherId` ORDER BY D.temp DESC LIMIT 5
The Hottest City	The hottest city	SELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp DESC LIMIT 1
The Coldest City	The coldest city	SELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp ASC LIMIT 1
7 Day Forecast	7 Day Forecast using parameters	SELECT C.cityName, MAX(CASE WHEN W.detailType = 'current' THEN 'Current' END) AS day0, MAX(CASE WHEN W.detailType = 'current' THEN W.temp END) AS temp0, MAX(CASE WHEN W.detailType = 'current' THEN X.type END) AS condition0, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'current' THEN X.filename END)) AS icon0, MAX(CASE WHEN W.detailType = 'day1' THEN W.dayOfWeek END) AS day1, MAX(CASE WHEN W.detailType = 'day1' THEN W.temp END) AS temp1, MAX(CASE WHEN W.detailType = 'day1' THEN W.temp2 END) AS low1, MAX(CASE WHEN W.detailType = 'day1' THEN W.pop END) AS pop1, MAX(CASE WHEN W.detailType = 'day1' THEN X.type END) AS condition1, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day1' THEN X.filename END)) AS icon1, MAX(CASE WHEN W.detailType = 'day2' THEN W.dayOfWeek END) AS day2, MAX(CASE WHEN W.detailType = 'day2' THEN W.temp END) AS temp2, MAX(CASE WHEN W.detailType = 'day2' THEN W.temp2 END) AS low2, MAX(CASE WHEN W.detailType = 'day2' THEN W.pop END) AS pop2, MAX(CASE WHEN W.detailType = 'day2' THEN X.type END) AS condition2, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day2' THEN X.filename END)) AS icon2, MAX(CASE WHEN W.detailType = 'day3' THEN W.dayOfWeek END) AS day3, MAX(CASE WHEN W.detailType = 'day3' THEN W.temp END) AS temp3, MAX(CASE WHEN W.detailType = 'day3' THEN W.temp2 END) AS low3, MAX(CASE WHEN W.detailType = 'day3' THEN W.pop END) AS pop3, MAX(CASE WHEN W.detailType = 'day3' THEN X.type END) AS condition3, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day3' THEN X.filename END)) AS icon3, MAX(CASE WHEN W.detailType = 'day4' THEN W.dayOfWeek END) AS day4, MAX(CASE WHEN W.detailType = 'day4' THEN W.temp END) AS temp4, MAX(CASE WHEN W.detailType = 'day4' THEN W.temp2 END) AS low4, MAX(CASE WHEN W.detailType = 'day4' THEN W.pop END) AS pop4, MAX(CASE WHEN W.detailType = 'day4' THEN X.type END) AS condition4, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day4' THEN X.filename END)) AS icon4, MAX(CASE WHEN W.detailType = 'day5' THEN W.dayOfWeek END) AS day5, MAX(CASE WHEN W.detailType = 'day5' THEN W.temp END) AS temp5, MAX(CASE WHEN W.detailType = 'day5' THEN W.temp2 END) AS low5, MAX(CASE WHEN W.detailType = 'day5' THEN W.pop END) AS pop5, MAX(CASE WHEN W.detailType = 'day5' THEN X.type END) AS condition5, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day5' THEN X.filename END)) AS icon5, MAX(CASE WHEN W.detailType = 'day6' THEN W.dayOfWeek END) AS day6, MAX(CASE WHEN W.detailType = 'day6' THEN W.temp END) AS temp6, MAX(CASE WHEN W.detailType = 'day6' THEN W.temp2 END) AS low6, MAX(CASE WHEN W.detailType = 'day6' THEN W.pop END) AS pop6, MAX(CASE WHEN W.detailType = 'day6' THEN X.type END) AS condition6, CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day6' THEN X.filename END)) AS icon6 FROM weather_city_details W JOIN weather_city C ON C.id=W.cityID JOIN weather X ON X.ID=W.weatherID WHERE W.detailType IN ('current', 'day1', 'day2', 'day3', 'day4', 'day5', 'day6') AND C.cityName=@City GROUP BY W.cityID

Branding Schedule Samples

Prime Time Schedule Lineup

Prime Time Schedule

SELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'
AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00') AND Time(S.start)<Time('23:00:00')

SELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S

WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'

AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00')

Branding - Next on Channel

Next on Channel using parameters

SELECT P.name, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name=@Channel

/*Demo Central XPR*/

AND S.start > now()
ORDER BY S.start LIMIT 1

In this section:

BannisterLake.com