CODEWITHSUNDEEP
sql
Stefan Prammer
Stefan Prammer

Reputation: 11

SELECT statement with operators not working

I have these two tables and I need a query, that outputs every member that has the lvnr 050056 AND NOT 050054.

I have these two tables

enter image description here

I have tried it with the following query but it does not work right:

SELECT s.matrnr, s.vorname, s.nachname
FROM student s
INNER JOIN  teilgenommen t ON s.matrnr = t.matrnr
WHERE (t.lvnr = 050056) AND (t.lvnr != 050054)

Only Martin Huber with the ID 0111111 should be shown, but I get both..

I would be very thankful for any advie

Upvotes: 0

Views: 55

Answers (3)

GMB
GMB

Reputation: 222482

This can also be solved with aggregation and a having clause for filtering:

select s.matrnr, s.vorname, s.nachname
from student s
inner join  teilgenommen t on s.matrnr = t.matrnr
group by s.matrnr, s.vorname, s.nachname
having
    max(case when t.lvnr = 050056 then 1 else 0 end) = 1
    and max(case when t.lvnr = 050054 then 1 else 0 end) = 0

Upvotes: 0

Georgina Skibinski
Georgina Skibinski

Reputation: 13387

You can do:

SELECT 
   s.matrnr, 
   s.vorname, 
   s.nachname
FROM 
   student s
INNER JOIN  
   (
      select 
         matrnr, 
         max(case when lvnr='050056' then 1 else 0 end) as a, 
         max(case when lvnr='050054' then 1 else 0 end) as b 
      from 
         teilgenommen 
      group by 
         matrnr 
      having a=1 and b=0
   ) t
ON s.matrnr = t.matrnr

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

Use exists and not exists:

select s.*
from student s
where exists (select 1
              from teilgenommen t 
              where t.matrnr = s.matrnr and t.lvnr = '050056'
             ) and
      not exists (select 1
                  from teilgenommen t 
                  where t.matrnr = s.matrnr and t.lvnr = '050054'
                 );

The leading zeros suggest that lvnr is really stored as a string. If so, then single quotes should be used for the comparison value.

Upvotes: 2

Related Questions