User:Taweiss

Active when I have time. Should be at least weekly. Trying different SQL queries.

Dump my PVs

SELECT

   (SELECT 
           GROUP_CONCAT(CONCAT(a.author_lastname,
                           ', ',
                           TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1)))
                   SEPARATOR '|')
       FROM
           authors a,
           pub_authors pa
       WHERE
           pubs.pub_id = pa.pub_id
               AND a.author_id = pa.author_id) author_details,
   pubs.pub_title title,
   pubs.pub_isbn isbn,
   pb.publisher_name publisher,
   CASE
       WHEN pubs.pub_year = '0000-00-00' THEN NULL
       WHEN EXTRACT(MONTH FROM pubs.pub_year) = 0 THEN SUBSTRING(pubs.pub_year, 1, 4)
       WHEN EXTRACT(DAY FROM pubs.pub_year) = 0 THEN SUBSTRING(pubs.pub_year, 1, 7)
       ELSE pubs.pub_year
   END AS 'date_published',
   CASE
       WHEN
           ps.pub_series_name IS NOT NULL
               AND pubs.pub_series_num IS NOT NULL
       THEN
           CONCAT(ps.pub_series_name,
                   ' (',
                   pubs.pub_series_num,
                   ')')
       ELSE ps.pub_series_name
   END AS 'series_details',
   pubs.pub_pages pages,
   CASE
       WHEN pubs.pub_ctype = 'ANTHOLOGY' THEN '1'
       ELSE '0'
   END AS 'anthology',
   pubs.pub_ptype 'format',
   ts.series_title,
   t.title_seriesnum

FROM

   primary_verifications v
       INNER JOIN
   pubs ON pubs.pub_id = v.pub_id
       INNER JOIN
   pub_content pc ON pubs.pub_id = pc.pub_id
       INNER JOIN
   titles t ON t.title_id = pc.title_id
       LEFT OUTER JOIN
   publishers pb ON pb.publisher_id = pubs.publisher_id
       LEFT OUTER JOIN
   pub_series ps ON ps.pub_series_id = pubs.pub_series_id
       LEFT OUTER JOIN
   series ts ON ts.series_id = t.series_id

WHERE

   pubs.pub_ctype IN ('COLLECTION' , 'ANTHOLOGY',
       'MAGAZINE',
       'NOVEL',
       'CHAPBOOK',
       'OMNIBUS',
       'NONFICTION')
       AND ((t.title_ttype = pubs.pub_ctype)
       OR (t.title_ttype = 'EDITOR'
       AND pubs.pub_ctype = 'MAGAZINE'))
       AND v.user_id = 56100

ORDER BY pubs.pub_title INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\book_list.csv' CHARACTER SET UTF8MB4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

Seeing if I can find the most reprinted short story.

SELECT

   (SELECT 
           GROUP_CONCAT(CONCAT(a.author_lastname,
                           ', ',
                           TRIM(SUBSTRING_INDEX(a.author_canonical, a.author_lastname, 1)))
                   SEPARATOR '|')
       FROM
           authors a,
           canonical_author pa
       WHERE
           t.title_id = pa.title_id
               AND a.author_id = pa.author_id) author_details,
   t.title_title,
   pubs.pub_title, pubs.pub_isbn,
   COUNT(t.title_title) AS dupe_cnt

FROM

   titles t
       INNER JOIN
   pub_content pc ON t.title_id = pc.title_id
       INNER JOIN
   pubs ON pubs.pub_id = pc.pub_id

WHERE

   t.title_title BETWEEN 'A' AND 'z'
       AND t.title_ttype = 'SHORTFICTION'

GROUP BY author_details , t.title_title HAVING COUNT(t.title_title) > 50 ;