Web lists-archives.com

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




On Fri, 19 Jan 2018 11:25:42 -0500, shawn l.green wrote:

> 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.

Hi Shawn,
That works a treat! Thank you for a solution with an explanation, which I 
appreciate, because it helps me learn.
Regards, Chris Roy-Smith


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