Re: Can't get my query to return wanted data

Hello Chris,

On 1/19/2018 12:50 AM, Chris Roy-Smith wrote:
> Hi
> I am running mysql 5.7.20 in ubuntu linux 17.10
>
> I have 2 tables, member and status with contents like
>
> member:
> ident, 	given, 	surname
> 1	fred	jones
> 2	john	howard
> 3	henry	wales
> 4	jenny	brown
>
> status:
> ident	year
> 1	2017
> 2	2017
> 3	2017
> 4	2017
> 1	2018
> 3	2018
>
> I want my query to return the name and ident from the member table for all
> members that has not got an entry in status with year=2018
>
> I have been working on the following query to achieve this, but it only
> returns data when there is no `year` entries for a selected year.
>
> select details.ident, given, surname from details left join status on
> details.ident = status.ident where NOT EXISTS (select year from status
> where (status.year = 2018) and (details.ident = status.ident) )
>
>
> Thank you for looking at this.
> regards, Chris Roy-Smith
>
>

try this...

SELECT
   d.ident, d.given, d.surname
FROM details d
LEFT JOIN (
   SELECT DISTINCT ident
   FROM status
   WHERE year=2018
) s
   ON s.ident = d.ident
WHERE
   s.ident is NULL;

How it works....
#
Start by building a list of unique `ident` values that match the 
condition you do NOT want to find. (you will see why in a moment)

LEFT JOIN that list to your list of members (with your list on the right 
side of the LEFT JOIN).  Where that join's ON condition is satisfied, a 
value for the column s.ident will exist. Where it isn't satisfied, there 
will be a NULL value in s.ident.

Finally, filter the combination of the s and d tables (I'm referring to 
their aliases) to find all the rows where s.ident was not given a value 
because it did not satisfy the ON condition of your outer join.
#

Yours,
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql