0

I have two MySQL tables table1 and table 2.

Table 1:-

batch    semester  scode
IT         6        DA
IT         6        IA
IT         6        FA

Table 2:-

batch    subject   user
IT        DA       1

I want to run a query to get this output:-

scode
IA
FA

So basically I want to retrieve values from table 1 which does not exist in table2 for a user with id 1 and where the batch is IT and the semester is 6. I am new to the concepts of fetching data from two tables so I am unable to think of a way to do this. Any help is highly appreciated.

1000111
  • 13,169
  • 2
  • 28
  • 37
Kartikey Vishwakarma
  • 499
  • 2
  • 8
  • 18
  • Possible duplicate of [Exclude Certain Records with Certain Values SQL Select](http://stackoverflow.com/questions/38983582/exclude-certain-records-with-certain-values-sql-select) – demo Dec 11 '16 at 11:41

1 Answers1

1

You can use NOT EXISTS

SELECT 
scode
FROM TABLE1 T1 
WHERE NOT EXISTS (
   SELECT 1 FROM TABLE2 T2 WHERE T1.scode = T2.subject
);

SEE DEMO

You can use NOT IN

SELECT 
scode
FROM TABLE1 T1 
WHERE T1.scode NOT IN (
   SELECT T2.subject FROM TABLE2 T2 
)

SEE DEMO

1000111
  • 13,169
  • 2
  • 28
  • 37
  • I need to select the scode on the basis of the batch and the semester. – Kartikey Vishwakarma Dec 11 '16 at 10:05
  • How these two tables are related? – 1000111 Dec 11 '16 at 10:08
  • 1
    @KartikeyVishwakarma . . . You changed the question after this answer was posted. That is rude, because this answer is now out-of-date and someone who went out of their way to help you could get downvotes. I would suggest that you retract your edits, accept this answer, and ask another question. – Gordon Linoff Dec 11 '16 at 12:37