SELECT B.beer_uuid, old_beer_id as parent_old_beer_id, beer_name, BW.brewery_uuid, brewery_name, abv, BC.ibu, brewery_url, BW.town, BW.city, BW.county, coalesce(colour, '#F0ED68') AS colour, BC.beer_container_type_id, BCTN.description AS tasting_notes, BCIN.description AS ingredients, BCDC.description AS description, B.beer_type_id, B.beer_group_id, BC.beer_container_id, beer_container_type FROM beer B INNER JOIN beer_container BC ON BC.beer_uuid = B.beer_uuid INNER JOIN beer_container_type BCT ON BCT.beer_container_type_id = BC.beer_container_type_id INNER JOIN beer_brewery BB ON BB.beer_uuid = B.beer_uuid INNER JOIN (SELECT brewery_uuid, brewery_name, town, city, county, '/real-ale-brewery/' || url AS brewery_url FROM brewery WHERE url = 'ampersand-brew-co-bungay-nr35-2ah') BW ON BW.brewery_uuid = BB.brewery_uuid LEFT JOIN beer_type BT ON BT.beer_type_id = B.beer_type_id LEFT JOIN beer_description BCTN ON BCTN.beer_uuid = B.beer_uuid AND BCTN.beer_description_type_id = 1 LEFT JOIN beer_description BCIN ON BCIN.beer_uuid = B.beer_uuid AND BCIN.beer_description_type_id = 2 LEFT JOIN beer_description BCDC ON BCDC.beer_uuid = B.beer_uuid AND BCDC.beer_description_type_id = 3 WHERE B.url || coalesce(BCT.url, '') = 'noise-complaints-bottle'