Skip to content

Instantly share code, notes, and snippets.

@habdenscrimen
Created March 22, 2020 20:54
Show Gist options
  • Save habdenscrimen/68407751e62b5ba071c9ddbd4b69c969 to your computer and use it in GitHub Desktop.
Save habdenscrimen/68407751e62b5ba071c9ddbd4b69c969 to your computer and use it in GitHub Desktop.
SELECT
HighlightID as ID,
COALESCE(Title, '') AS BookName,
COALESCE(Authors, '') AS Authors,
json_extract(Highlight, '$.text') AS Highlight,
BookID,
TimeEdit
FROM
Books
INNER JOIN (
SELECT
OID AS BookID,
Highlight,
HighlightID,
TimeEdit
FROM
Items
INNER JOIN (
SELECT
ParentID,
Highlight,
HighlightID,
TimeEdit
FROM
Items
INNER JOIN (
SELECT
OID as HighlightID,
ItemID,
Val AS Highlight,
TimeEdt as TimeEdit
FROM
Tags
WHERE
TagID = 104
AND Val <> '{\"text\":\"Bookmark\"}'
) AS Highlights ON Highlights.ItemID = OID
) AS Highlights ON Highlights.ParentID = OID
) AS Highlights ON BookID = OID;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment