[tngusers2] SQL query help needed

[Bruce Roy]

Warren, I have a similar report created within TNG's own report template (I don't have an address field for burials so it is a simple report) but I would like to sort the burial place in backwards order (country first). Is there a simple SQL query for that? I don't want to display a separate column for each component (country, state, etc), just sort the place column.

Thanks
Bruce Roy

On 01/04/2010, at 8:52 , Warren S Gilbert wrote:

> Jerry,
>  
> Try this.
>  
> SELECT CONCAT(UPPER(tng_people.lastname), ', ', tng_people.firstname) AS name,tng_people.personID,birthdate,deathdate,burialdate,burialplace,TRIM(CONCAT(A1.address1,', ',A1.address2,', ',A1.city,', ',A1.state)) as burialaddress, (if(sex='M',F1.wife,F2.husband)) as spouse FROM (tng_people) LEFT JOIN tng_families AS F1 ON tng_people.gedcom = F1.gedcom AND tng_people.personID = F1.husband LEFT JOIN tng_families AS F2 ON tng_people.gedcom = F2.gedcom AND tng_people.personID = F2.wife LEFT JOIN tng_events AS E1 ON tng_people.gedcom = E1.gedcom AND tng_people.personID = E1.persfamID LEFT JOIN tng_eventtypes AS E2 ON E1.eventtypeID = E2.eventtypeID LEFT JOIN tng_addresses AS A1 ON E1.gedcom = A1.gedcom AND E1.addressID = A1.addressID WHERE (E2.tag = 'BURI' AND A1.address1 != "") ORDER BY burialdatetr
>  
> Regards,
>  
> Warren Gilbert
>  
> www.gilbert-family-history.net

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lythgoes.net/pipermail/tngusers2_lythgoes.net/attachments/20100401/d9f01444/attachment.html>