SELECT 
    t1.bibNumber AS A_bib,
    t1.title as A_title,
    FLOOR(t1.deweyClass) AS A_dewey,
    t3.subject AS A_subject,

    t2.bibNumber AS B_bib,
    t2.title as B_title,
    FLOOR(t2.deweyClass) AS B_dewey,
    t4.subject AS B_subject

	
FROM
    (SELECT 
        bibNumber,
            GROUP_CONCAT(subject
                SEPARATOR ';') AS subject
    FROM
        spl_2016.subject
    WHERE
		subject REGEXP '^[0-9a-zA-Z .]+$'
        -- filter out non-English subjects
		-- The anchor ^ and $ ensure that you are matching the entire string and not part of it. 
		-- Next the character class [0-9a-zA-Z .] matches a single upper/lower case letter or a space or a period. 
		-- The + is the quantifier for one or more repetitions of the previous sub-regex.
		-- so in this case it allows us to match one or more of either a period or a space or a upper/lower case letter.
    GROUP BY bibNumber) AS t3,
    
    (SELECT 
        bibNumber,
            GROUP_CONCAT(subject
                SEPARATOR ';') AS subject
    FROM
        spl_2016.subject
    WHERE
		subject REGEXP '^[0-9a-zA-Z .]+$'
        -- filter out non-English subjects
		-- The anchor ^ and $ ensure that you are matching the entire string and not part of it. 
		-- Next the character class [0-9a-zA-Z .] matches a single upper/lower case letter or a space or a period. 
		-- The + is the quantifier for one or more repetitions of the previous sub-regex.
		-- so in this case it allows us to match one or more of either a period or a space or a upper/lower case letter.
    GROUP BY bibNumber) AS t4,
    
    spl_2016.inraw t1
        INNER JOIN
    spl_2016.inraw t2 ON t1.cout = t2.cout AND t1.cin = t2.cin
		AND t1.title LIKE '%architecture%'
        AND t1.bibNumber != t2.bibNumber
        AND t1.deweyClass != ''
        AND t2.deweyClass != ''
--         AND YEAR(t1.cout) > 2017

WHERE
    t1.bibNumber = t3.bibNumber
        AND t2.bibNumber = t4.bibNumber