-- Selects Double, 2 Photos from LASN that are approved
SELECT 
    -- picture, company, text, phone, web
    lgr.company_name, lgr.content, lgr.phone, lgr.website, 
    CONCAT(lgm_l.basename,'.',lgm_l.format), 
    CONCAT(lgm_r.basename,'.',lgm_r.format)
    -- LEFT(lgr.photos, LOCATE(',',lgr.photos)-1),
    -- RIGHT(lgr.photos, LOCATE(',',lgr.photos)-1)
    -- lg.id, lg.vendor_id, lgr.id, lg.status, lg.size, lgc.name, lg.magazines, lg.year, lgr.company_name
    INTO OUTFILE '/tmp/lasn-doubles-2-20050331.csv'
         FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
         LINES TERMINATED BY '\n'
FROM    
    lead_generator as lg
LEFT JOIN 
    lead_generator_categories as lgc ON lg.category=lgc.id 
LEFT JOIN
    -- if we don't filter this here, we'll get a result for each lga column
    lead_generator_approvals  as lga ON (lg.id=lga.lg_id AND lga.status='approved')
LEFT JOIN
    lead_generator_revision   as lgr ON ( lga.rev_id=lgr.id )
-- We have to get the media somehow
LEFT JOIN
   lead_generator_media      as lgm_l ON ( LEFT(lgr.photos, LOCATE(',',lgr.photos) - 1) = lgm_l.id)
LEFT JOIN
   lead_generator_media      as lgm_r ON ( RIGHT(lgr.photos, LOCATE(',',lgr.photos) - 1) = lgm_r.id)
WHERE
        lg.status = 'approved'
    AND
        lg.size   = 'double'
    AND
        lg.magazines = 'lasn'
    AND 
        lgr.id = lga.rev_id
    AND 
        LOCATE(',',lgr.photos) > 0
ORDER BY
    lgr.company_name ASC
