Simple Select Sql Query Not Working

Just thought of learning SQL on my own and started with THIS I was practicing some tutorial and got stuck in one basic query. After trying a lot I could not get it running. Quest

Solution 1:

SELECT yr 
FROM nobel
WHERE subject ='Physics'AND
yr NOTIN (Select yr from nobel where subject ='Chemistry') 

Solution 2:

So close! You just needed yr and IN:

SELECT yr 
FROM nobel
WHERE subject ='Physics'AND
    yr NOTIN (Select yr from nobel where subject ='Chemistry') 

You could also do this with an exclusion join:

SELECT yr
FROM nobel n1
LEFTJOIN nobel n2 ON n1.yr = n2.yr AND n2.subject ='Chemistry'WHERE n1.subject ='Physics'AND n2.subject isNULL

or a NOT EXISTS

SELECT yr 
FROM nobel n1
WHERE n1.subject ='Physics'ANDNOTEXISTS 
  (
      SELECTNULLFROM nobel n2 
      WHERE n2.subject ='Chemistry'AND n2.yr=n1.yr
  )

Solution 3:

You could also use LEFT [OUTER] JOIN, to make sure that no row for the same year exists:

SELECT yr 
FROM   nobel p
LEFTJOIN nobel c ON c.yr = p.yr
                   AND c.subject ='Chemistry'WHERE  p.subject ='Physics'AND    c.yr ISNULL;

There are basically 4 techniques:

• Select rows which are not present in other table

Solution 4:

I believe you don't need that second AND condition; cause with that second condition you are just trying to exclude all yr where subject is chemistry. Your query can simply be

SELECT yr 
FROM nobel
WHERE subject ='Physics'

Solution 5:

Try This :

SELECT yr 
FROM nobel
WHERE subject ='Physics'AND yr
NOTIN(Select yr from nobel where subject ='Chemistry') GROUPBY yr desc