Remove results of one query from the second

1

I have 2 queries that work
#1 results all the people that have died in Missouri between 1-1-1910 and 12-31-1965
#2 results all the people that have died in Missouri between 1-1-1910 and 12-31-1965, but have death certificates associated with them

How can I remove those resulted in #2 from the results in #1
Basically what I'm looking for is people that have died in Missouri between 1-1-1910 and 12-31-1965, but have NO death certificates associated with them

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

#2
SELECT tng_people.personID, lastname, firstname, birthdatetr, deathdatetr, deathplace, tng_medialinks.personID, tng_medialinks.mediaID, tng_media.mediaID, tng_media.description
FROM tng_people
JOIN tng_medialinks
ON tng_people.personID = tng_medialinks.personID
JOIN tng_media
ON tng_medialinks.mediaID = tng_media.mediaID
WHERE (deathplace LIKE "%missouri%")
AND (deathdatetr BETWEEN "1910-01-01" AND "1965-12-31")
AND (tng_media.description LIKE '%death certificate%')
ORDER BY lastname ASC

2

Practically the same question as this.

select p.personID
      ,p.lastname
      ,p.firstname
      ,p.birthdatetr
      ,p.deathdatetr
      ,p.deathplace
  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%"
   and ml.eventID is null
 order by p.lastname
         ,p.firstname
;
3

The problem is there are other media types that can be associated with "DEAT" ml.eventID
So the NULL will not work
It come be an Obituary linked to the ml.eventID.DEAT field

4

Then change it to:

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

Maybe this is what you are looking for?

SELECT id, lastname, firstname, birthdatetr, deathdatetr, deathplace
FROM tng_people
WHERE deathplace LIKE "%missouri%"
AND deathdatetr BETWEEN "1910-01-01" AND "1965-12-31"
AND NOT EXISTS (SELECT * FROM tng_medialinks ml INNER JOIN tng_media m ON m.mediaID = ml.mediaID WHERE ml.personID = tng_people.personID AND m.description LIKE '%death certificate%')
ORDER BY lastname ASC

Another option:

WITH cte
AS (
SELECT tng_people.personID, lastname, firstname, birthdatetr, deathdatetr, deathplace, tng_medialinks.personID, tng_medialinks.mediaID, tng_media.mediaID, tng_media.description
,CASE WHEN tng_media.description LIKE '%death certificate%' THEN 1 ELSE 0 END AS death_certificate
FROM tng_people
JOIN tng_medialinks
ON tng_people.personID = tng_medialinks.personID
JOIN tng_media
ON tng_medialinks.mediaID = tng_media.mediaID
WHERE (deathplace LIKE "%missouri%") 
AND (deathdatetr BETWEEN "1910-01-01" AND "1965-12-31")
)
SELECT *
FROM cte
WHERE death_certificate = 0
ORDER BY lastname ASC
6

This is what I get when I run the Code:

Screen Shot 2016-12-26 at 6.28.10 AM.png2540×1194 196 KB

7

Well - that is because I gave you Microsoft SQL Server syntax - not MySQL. Unfortunately, MySQL does not support common table expressions.

You can rewrite this using a derived table - as in:

SELECT *
FROM (
SELECT tng_people.personID, lastname, firstname, birthdatetr, deathdatetr, deathplace, tng_medialinks.personID, tng_medialinks.mediaID, tng_media.mediaID, tng_media.description
,CASE WHEN tng_media.description LIKE '%death certificate%' THEN 1 ELSE 0 END AS death_certificate
FROM tng_people
JOIN tng_medialinks
ON tng_people.personID = tng_medialinks.personID
JOIN tng_media
ON tng_medialinks.mediaID = tng_media.mediaID
WHERE (deathplace LIKE "%missouri%") 
AND (deathdatetr BETWEEN "1910-01-01" AND "1965-12-31")
) As der
WHERE der.death_certificate = 0
ORDER BY der.lastname ASC
8

Once again an ugly error?

Screen Shot 2016-12-27 at 8.20.34 PM.png2514×1216 161 KB

9

So why do you have that column in your derived table multiple times - is it needed? If so then you have to name that column (alias) with a different name - if not, remove the column.

10

It is a Key in 2 different tables.
Actually a key in Many different tables.

11

Okay - but do you need it multiple times in this query? That is the error you are getting from the derived table which cannot return the same column name twice...remove one of them from the derived table query.

12

tng_people is associated to tng_medialinks by personID

tng_medialinks is associated to tng_media by mediaID

To get the media associated to the tng_people.personID you first have to associate to the tng_mediaID affiliated to the tng_medialinks.personID and associate that to the tng_people.personID by tng_medialinks.personID

You wrote the query
I simply showed the error I received when I ran it

If you know a different way to write the query I'm open...

13

Again - remove the second personID column from being returned in the derived table. [quote="jeffw8713, post:7, topic:8914"]
SELECT tng_people.personID, lastname, firstname, birthdatetr, deathdatetr, deathplace, tng_medialinks_.personID, tng_medialinks.mediaID, tng_media.mediaID, tng_media.description
[/quote]

This is your system - and your solution - I am only trying to help you get there but that does require some level of effort on your part.