ISCA Ales

SELECT user_venue_id, old_user_id, bagged, username, gender_id, CASE WHEN V.venue_uuid IS NULL THEN FD.festival_date_name ELSE venue_name END AS venue_name, CASE WHEN V.venue_uuid IS NULL THEN F.town ELSE V.town END AS town, CASE WHEN V.venue_uuid IS NULL THEN F.city ELSE V.city END AS city, CASE WHEN V.venue_uuid IS NULL THEN F.county ELSE V.county END AS county, CASE WHEN V.venue_uuid IS NULL THEN 'festival' ELSE venue_establishment_type END AS venue_establishment_type, CASE WHEN V.venue_uuid IS NULL THEN '/beer-festival/' || F.festival_url ELSE '/pub/' || VN.venue_url END AS venue_url, 0 AS distance, B.beer_name, '/real-ale-brewery/' || BW.url || '/' || B.url || COALESCE(BCT.url, '') AS beer_url, brewery_name, '/real-ale-brewery/' || BW.url AS brewery_url, COALESCE('/images/users/' || IU.image_url,'https://graph.facebook.com/' || faceBook || '/picture?width=361') AS user_image, COALESCE('/images/pubs/' || IV.image_url,'/images/pubs/' || IF.image_url) AS venue_image, '/images/beers/' || IB.image_url AS beer_image, '/images/breweries/' || IBW.image_url AS brewery_image FROM (SELECT * FROM user_venue ORDER BY bagged DESC) UV INNER JOIN user_account UA ON UA.user_account_uuid = UV.user_account_uuid LEFT JOIN venue_name VN ON VN.venue_name_id = UV.venue_name_id LEFT JOIN venue V ON V.venue_uuid = VN.venue_uuid LEFT JOIN venue_establishment_type VET ON VET.venue_establishment_type_id = V.venue_establishment_type_id LEFT JOIN festival_date FD ON FD.festival_date_id = UV.festival_date_id LEFT JOIN festival F ON F.festival_uuid = FD.festival_uuid LEFT JOIN beer_container BC ON BC.beer_container_id = UV.beer_container_id LEFT JOIN beer B ON B.beer_uuid = BC.beer_uuid LEFT JOIN beer_container_type BCT ON BCT.beer_container_type_id = BC.beer_container_type_id LEFT JOIN beer_brewery BB ON BB.beer_uuid = B.beer_uuid LEFT JOIN brewery BW ON BW.brewery_uuid = BB.brewery_uuid AND brewery_uuid = '{B6C2BB53-6147-4079-8AD1-C10CF0F6D3CD}' LEFT JOIN image IU ON IU.user_account_uuid = UV.user_account_uuid AND IU.image_sequence = 1 LEFT JOIN image IV ON IV.venue_name_id = UV.venue_name_id AND IV.image_sequence = 1 LEFT JOIN image IF ON IF.festival_date_id = UV.festival_date_id AND IF.image_sequence = 1 LEFT JOIN image IB ON IB.beer_container_id = UV.beer_container_id AND IB.image_sequence = 1 LEFT JOIN image IBW ON IBW.brewery_uuid = BW.brewery_uuid AND IBW.image_sequence = 1 ORDER BY bagged DESC LIMIT 12