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 fredjones 2 johnhoward 3 henry wales 4 >> jennybrown >> >> status: >> identyear 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
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 fredjones 2 johnhoward 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
Can't get my query to return wanted data
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 fredjones 2 johnhoward 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql