Topic on Talk:Quarry

Has anyone an idea what I can do to resolve the error "Unknown column 'el_to' in 'field list'" for the following query:

use commonswiki_p;

SELECT concat("M",page_id) AS MID, "P6305",

concat('"',REPLACE(el_to,'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=',''),'"') AS OTRS,

    count(el_to) as num

FROM page

JOIN categorylinks c1 ON page_id=c1.cl_from AND c1.cl_to='Files_with_PermissionTicket_template_but_without_P6305_SDC_statement'

JOIN templatelinks ON page_id=tl_from AND tl_target_id = 242

JOIN externallinks ON page_id=el_from AND el_to LIKE 'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'

group by MID

having num=1

el_to has been split into el_to_domain_index and el_to_path. Note that el_to_domain_index is an inverted index. Your query should now look like WHERE el_to_domain_index = 'https://org.wikimedia.ticket.' AND el_to_path LIKE '/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'.

Thank you! That worked.