SELECT distinct(av.object_id),
last_name.text as last_name,
first_names.text as first_names,
attendee_email.text as attendee_email

FROM ams_attribute_values av

--lastname
INNER JOIN (select amt.text,av1.object_id,av1.attribute_id from ams_texts amt
		INNER JOIN ams_attribute_values av1 ON amt.value_id = av1.value_id
		WHERE av1.attribute_id = (select attribute_id from ams_attributes where attribute_name = 'last_name')) as last_name
ON last_name.object_id = av.object_id
--firstname
INNER JOIN (select amt.text,av1.object_id,av1.attribute_id from ams_texts amt
		INNER JOIN ams_attribute_values av1 ON amt.value_id = av1.value_id
		WHERE av1.attribute_id = (select attribute_id from ams_attributes where attribute_name = 'first_names')) as first_names
ON first_names.object_id = av.object_id
--attendee email
FULL JOIN (select amt.text,av1.object_id,av1.attribute_id from ams_texts amt
		INNER JOIN ams_attribute_values av1 ON amt.value_id = av1.value_id
		WHERE av1.attribute_id = (select attribute_id from ams_attributes where attribute_name = 'attendee_email')) as attendee_email
ON attendee_email.object_id = av.object_id
-- check to see if party's last name is used elsewhere
WHERE (select count(*) from ams_attribute_values av2 inner join ams_texts amt2 on av2.value_id=amt2.value_id where amt2.text=last_name.text
	and av2.attribute_id=last_name.attribute_id) > 1

order by last_name