I’d like to know how to find the names (namn) of the people who have taught on a course (kkod) but have never been course coordinator (kansv). Is it a minus operation (not exists operation)? The result should look like:
namn Jonas Marie Rikard Urban
The results above should be retrieved from the two following tables: table tf;
kkod termin kansv antal ------------------------------ --------------- ---------- ---------- TIG015 ht2010 Kalle 90 TIG015 ht2011 Kalle 90 TIG015 ht2012 Kalle 85 TIG015 ht2013 Kalle 88 TIG015 ht2014 Magnus 110 TIG015 ht2015 Aida 100 TIG162 ht2010 Kjell 90 TIG162 ht2011 Kjell 85 TIG162 ht2012 Kjell 85 TIG162 ht2014 Lisen 110 TIG058 ht2010 Janne 80 TIG058 ht2012 Lennart 80 TIG058 ht2015 Lennart 80 TIG058 ht2016 Alan 85 TIG059 vt2012 Janne 75 TIA008 vt2015 Aida 35 TIG059 vt2013 Dina 75 TIG059 vt2014 Janne 75 TIG098 ht2015 Agneta 60 TIG016 ht2011 Janne 80 TIG016 ht2012 Maria 80 TIG016 ht2015 Maria 80 TIG016 vt2012 Maria 75 TIG163 ht2011 Lennart 75 TIG163 ht2012 Lennart 80 TIG163 ht2014 Lennart 90 TIG163 vt2012 Lennart 75 TIG164 ht2010 Juha 70 TIG164 ht2011 Alan 60 TIG164 ht2012 Alan 55 TIG164 ht2014 Alan 60 TIG164 ht2015 Alan 75 TIG064 ht2010 William 50 TIG064 vt2012 William 80 TIG064 vt2013 Faramarz 80 TIG064 vt2014 Faramarz 66 TIG064 vt2015 Faramarz 80 TIG064 vt2016 Faramarz 55 TIG065 vt2013 Kalle 25 TIG065 vt2014 Kalle 23 TIG065 vt2015 Kalle 19 TIG065 vt2016 Kalle 25 TIG166 ht2010 Fredrik 40 TIG167 vt2012 Janne 20 TIG167 vt2013 Janne 24 TIG167 vt2014 Henrik 33 TIG167 vt2015 Henrik 43 TIG167 vt2016 Henrik 32 TIG165 ht2011 William 80 TIG165 ht2012 Alan 80 TIG165 ht2014 Lennart 90 TIG067 ht2009 Johan 70 TIA003 ht2012 Dina 24 TIA003 ht2013 Dina 26 TIA003 ht2014 Dina 31 TIA003 ht2015 Dina 18 TIG061 ht2009 Lennart 85 TIG061 ht2010 Lennart 65 TIG061 vt2013 Lennart 35 TIG060 vt2012 Fredrik 25 TIG063 vt2012 Kjell 40 TIG063 vt2013 Kjell 13 TIG063 vt2014 Kjell 32 TIG063 vt2015 Kjell 40 TIG063 vt2016 Kjell 21 TIG066 vt2012 Janne 60 TIG066 vt2013 Janne 60 TIG066 vt2014 Janne 56 TIG066 vt2015 Janne 54 TIG066 vt2016 Janne 80 TIA010 ht2011 Juha 20 TIA004 ht2015 Janne 75 TIA015 ht2013 Maria 15 TIA019 ht2016 Janne 25
and table uv;
kkod termin namn ------------------------------ --------------- ---------- TIG098 ht2015 Agneta TIG015 ht2014 Aida TIG015 ht2015 Aida TIG058 ht2010 Alan TIG058 ht2012 Alan TIG058 ht2016 Alan TIG059 vt2014 Alan TIG059 vt2015 Alan TIG059 vt2016 Alan TIG163 ht2011 Alan TIG163 ht2012 Alan TIG163 vt2012 Alan TIG164 ht2010 Alan TIG164 ht2011 Alan TIG164 ht2012 Alan TIG164 ht2014 Alan TIG164 ht2015 Alan TIG165 ht2011 Alan TIG165 ht2012 Alan TIG059 vt2012 Dina TIG059 vt2013 Dina TIG167 vt2012 Dina TIG163 ht2011 Faramarz TIG163 vt2012 Faramarz TIG163 ht2012 Faramarz TIG163 ht2014 Faramarz TIG060 vt2012 Fredrik TIG166 ht2010 Fredrik TIG061 ht2010 Henrik TIG061 vt2013 Henrik TIG167 vt2014 Henrik TIG167 vt2015 Henrik TIG167 vt2016 Henrik TIG065 vt2014 Kalle TIG065 vt2015 Kalle TIG065 vt2016 Kalle TIG065 vt2014 Aida TIG065 vt2015 Aida TIG065 vt2016 Aida TIA004 ht2015 Janne TIA019 ht2016 Janne TIG016 ht2011 Janne TIG016 ht2012 Janne TIG016 ht2015 Janne TIG016 vt2012 Janne TIG058 ht2010 Janne TIG059 vt2012 Janne TIG059 vt2013 Janne TIG059 vt2014 Janne TIG059 vt2015 Janne TIG059 vt2016 Janne TIG066 vt2012 Janne TIG066 vt2013 Janne TIG066 vt2014 Janne TIG066 vt2015 Janne TIG066 vt2016 Janne TIG163 ht2011 Janne TIG167 vt2012 Janne TIG067 ht2009 Johan TIG015 ht2014 Jonas TIG015 ht2015 Jonas TIA010 ht2011 Juha TIG060 vt2012 Juha TIG164 ht2010 Juha TIG164 ht2011 Juha TIG164 ht2012 Juha TIG166 ht2010 Juha TIA004 ht2015 Kalle TIG015 ht2010 Kalle TIG015 ht2011 Kalle TIG015 ht2012 Kalle TIG015 ht2013 Kalle TIG015 ht2014 Kalle TIG016 ht2012 Kalle TIG016 ht2015 Kalle TIG016 vt2012 Kalle TIG059 vt2014 Kalle TIG059 vt2015 Kalle TIG059 vt2016 Kalle TIG067 ht2009 Kalle TIG015 ht2014 Kjell TIG015 ht2015 Kjell TIG063 vt2012 Kjell TIG063 vt2014 Kjell TIG063 vt2015 Kjell TIG063 vt2016 Kjell TIG162 ht2010 Kjell TIG162 ht2011 Kjell TIG162 ht2012 Kjell TIG167 vt2014 Kjell TIG167 vt2016 Kjell TIA015 ht2013 Lennart TIG015 ht2015 Lennart TIA003 ht2012 Dina TIA003 ht2013 Dina TIA003 ht2014 Dina TIA003 ht2015 Dina TIA003 ht2012 Aida TIA003 ht2013 Aida TIA003 ht2014 Aida TIA003 ht2015 Aida TIA008 vt2015 Aida TIG058 ht2012 Lennart TIG058 ht2015 Lennart TIG059 vt2014 Lennart TIG059 vt2015 Lennart TIG059 vt2016 Lennart TIG061 ht2010 Lennart TIG061 vt2013 Lennart TIG162 ht2012 Lennart TIG163 ht2011 Lennart TIG163 ht2012 Lennart TIG163 ht2014 Lennart TIG163 ht2015 Lennart TIG163 vt2012 Lennart TIG165 ht2011 Lennart TIG165 ht2012 Lennart TIG165 ht2014 Lennart TIG165 ht2015 Lennart TIA019 ht2016 Lisen TIG098 ht2015 Lisen TIG162 ht2012 Lisen TIG162 ht2014 Lisen TIG162 ht2015 Lisen TIG015 ht2014 Magnus TIG067 ht2009 Magnus TIG015 ht2015 Maria TIG016 ht2011 Maria TIG016 ht2012 Maria TIG163 ht2014 Maria TIG163 ht2015 Maria TIA015 ht2013 Marie TIG165 ht2014 Marie TIG165 ht2015 Marie TIG167 vt2015 Rikard TIA019 ht2016 Urban TIG015 ht2015 Urban TIG064 ht2010 William TIG064 vt2012 William TIG064 vt2013 William TIG165 ht2011 William
Appriciate the help!
Advertisement
Answer
In SQLIte, you can use except:
select kanst as namn from tf except select namn from uv;
Alternatively:
select distinct kanst as namn from tf where not exists (select 1 from uv where uv.namn = tf.kanst);