www.katabiblon.com
select
avn.iverse,
case when avn.book_cd = "Mk" and dgp.mk_section is null then concat(avn.book_cd, " ", avn.chapter, ":",avn.iverse) else tmk.forum_tag end,
ifnull(dgp.mk_crossref, gmk.modified_html),
gmk.variants_list_html,
ifnull(dgp.mk_crossref, ifnull(vwt_mk.user_html, tmk.normalized_xml)),
if(vwt_mk.tid is null, null,
(SELECT group_concat(concat("<sup>", y.reference_mark, "</sup> ", cast(y.user_html as binary)) order by y.reference_mark separator "\t")
FROM kata_wiki.wiki_translations_commentary AS y
WHERE y.tid = vwt_mk.tid
GROUP BY y.tid)
) AS row_tmk_commentary_html,
case when avn.book_cd = "Mt" and dgp.mt_section is null then concat(avn.book_cd, " ", avn.chapter, ":",avn.iverse) else tmt.forum_tag end,
ifnull(dgp.mt_crossref, gmt.modified_html),
gmt.variants_list_html,
ifnull(dgp.mt_crossref, ifnull(vwt_mt.user_html, tmt.normalized_xml)),
if(vwt_mt.tid is null, null,
(SELECT group_concat(concat("<sup>", y.reference_mark, "</sup> ", cast(y.user_html as binary)) order by y.reference_mark separator "\t")
FROM kata_wiki.wiki_translations_commentary AS y
WHERE y.tid = vwt_mt.tid
GROUP BY y.tid)
) AS row_tmt_commentary_html,
case when avn.book_cd = "Lk" and dgp.lk_section is null then concat(avn.book_cd, " ", avn.chapter, ":",avn.iverse) else tlk.forum_tag end,
ifnull(dgp.lk_crossref, glk.modified_html),
glk.variants_list_html,
ifnull(dgp.lk_crossref, ifnull(vwt_lk.user_html, tlk.normalized_xml)),
if(vwt_lk.tid is null, null,
(SELECT group_concat(concat("<sup>", y.reference_mark, "</sup> ", cast(y.user_html as binary)) order by y.reference_mark separator "\t")
FROM kata_wiki.wiki_translations_commentary AS y
WHERE y.tid = vwt_lk.tid
GROUP BY y.tid)
) AS row_tlk_commentary_html,
dgp.pericope_nm
from kata_system.all_verse_numbers avn
left join kata_system.document_gospelparallel AS dgp
on avn.chapter = case avn.book_cd when "Mk" then dgp.mk_chapter when "Mt" then dgp.mt_chapter when "Lk" then dgp.lk_chapter end
and avn.iverse = case avn.book_cd when "Mk" then ifnull(dgp.mk_iverse, dgp.mk_section) when "Mt" then ifnull(dgp.mt_iverse, dgp.mt_section) when "Lk" then ifnull(dgp.lk_iverse, dgp.lk_section) end
left join kata_text.grc_robinson_verses AS gmk on (gmk.book_cd, gmk.chapter, gmk.iverse) = (dgp.mk_book_cd, dgp.mk_chapter, dgp.mk_iverse)
left join kata_text.grc_robinson_verses AS gmt on (gmt.book_cd, gmt.chapter, gmt.iverse) = (dgp.mt_book_cd, dgp.mt_chapter, dgp.mt_iverse)
left join kata_text.grc_robinson_verses AS glk on (glk.book_cd, glk.chapter, glk.iverse) = (dgp.lk_book_cd, dgp.lk_chapter, dgp.lk_iverse)
left join kata_wiki.view_wiki_translations_verse AS vwt_mk
ON (vwt_mk.book_cd, vwt_mk.chapter, vwt_mk.iverse, vwt_mk.wiki_translation_cd) = (dgp.mk_book_cd, dgp.mk_chapter, dgp.mk_iverse, ?)
left join kata_wiki.view_wiki_translations_verse AS vwt_mt
ON (vwt_mt.book_cd, vwt_mt.chapter, vwt_mt.iverse, vwt_mt.wiki_translation_cd) = (dgp.mt_book_cd, dgp.mt_chapter, dgp.mt_iverse, ?)
left join kata_wiki.view_wiki_translations_verse AS vwt_lk
ON (vwt_lk.book_cd, vwt_lk.chapter, vwt_lk.iverse, vwt_lk.wiki_translation_cd) = (dgp.lk_book_cd, dgp.lk_chapter, dgp.lk_iverse, ?)
left join kata_text.eng_webhtm_verses AS tmk on (tmk.book_cd, tmk.chapter, tmk.iverse) = (dgp.mk_book_cd, dgp.mk_chapter, dgp.mk_iverse)
left join kata_text.eng_webhtm_verses AS tmt on (tmt.book_cd, tmt.chapter, tmt.iverse) = (dgp.mt_book_cd, dgp.mt_chapter, dgp.mt_iverse)
left join kata_text.eng_webhtm_verses AS tlk on (tlk.book_cd, tlk.chapter, tlk.iverse) = (dgp.lk_book_cd, dgp.lk_chapter, dgp.lk_iverse)
WHERE (avn.book_cd, avn.chapter, avn.iverse, avn.crossref) = (?, ?, ?, ?) order by case avn.book_cd when "Mk" then dgp.mk_section when "Mt" then dgp.mt_section when "Lk" then dgp.lk_section end, dgp.id, avn.iverse
ERROR (1054): Unknown column 'avn.crossref' in 'where clause'