Help with SQL Query

1

I have a small Genealogy Website that uses SQL
I have the following code to search one table for the following info:

SELECT id, lastname, firstname, birthdatetr, deathdatetr
FROM tng_people
WHERE (burialplace LIKE "%missouri%") AND (deathdatetr BETWEEN "1910-01-01" AND "1965-12-31")
ORDER BY lastname, firstname ASC

In another table "tng_media" column name "mediatype" I need to see if those selected in the above query have a "death Certificate" affiliated with them.

mediaID | mediatypeID | gedcom | form | path | description | placetaken | notes | owner | thumbpth | etc....
7574 | death |Cervenka1| JPG |Death Certificate - John Doe.jpg |Death Certificate - John Doe | | | | |

2

Please provide:

  • table definitions of both tables in the form of create statements
  • sample data from both tables in the form of insert statements
  • expected output from the sample data you provide
3

Here are screen shots of the tables involved:
As a new user I can only post one image each.
The others will be folllowing

tng_people Table.jpg4070×1766 860 KB

This is what I have so far and it works, but I only need one instance per Individual.
This selects multiple rows for the same person

SELECT tng_people.personID, lastname, firstname, birthdatetr, deathdatetr, deathplace, eventID, tng_medialinks.personID
FROM tng_people, tng_medialinks
WHERE (tng_people.personID = tng_medialinks.personID) AND (tng_medialinks.eventID NOT LIKE 'deat') AND (burialplace LIKE "%missouri%") AND (deathdatetr BETWEEN "1910-01-01" AND "1965-12-31")
ORDER BY lastname, firstname ASC

4

tng_media Table.jpg4166×1664 635 KB

5

tng_medialinks table.jpg2124×1288 338 KB

6

Expected output.jpg1646×1606 648 KB

7

Tyr this:

select p.personID
      ,p.lastname
      ,p.firstname
      ,p.birthdatetr
      ,p.deathdatetr
      ,p.deathplace
      ,ml.eventID
  from tng_people as p
       left outer join tng_medialinks as ml
                    on ml.personID=p.personID
                   and ml.eventID='DEAT'
 where p.deathdatetr>=cast('1910-01-01' as date)
   and p.deathdatetr< cast('1966-01-01' as date)
   and p.burialplace like "%missouri%"
 order by p.lastname
         ,p.firstname
;