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
