How to compare same column and multiple values in same table?

[mgdvicky]

Table1 fields and datas : (itemcode,itemdesc)
(101, aaa)
(102, bbb)
(103, ccc)
(104, eee)
(105, fff)
(106, ggg)
(107, hhh)

Table2 fields and datas : (itemcode, finperiod, finyear)
(101, 'jan-14', fy2014)
(101, 'feb-14', fy2014)
(102, 'jan-14', fy2014)
(102, 'feb-14', fy2014)
(103, 'jan-14', fy2014)
(104, 'feb-14', fy2014)
(105, 'mar-14', fy2014)
(101, 'mar-14', fy2014)
(102, 'feb-14', fy2014)

Query like this : To get itemcode where finperiod='jan-14' not in finperiod='feb-14'and finyear='fy2014' and itemdesc = 'aaa';

if above query executes then the output should be like this : 103.

I got this output but I used to separate this two table into multiple table that means to split tables like a_jan, a_feb, a_mar...

[Rabbit]

I don't understand what you're trying to do. What you say the criteria is doesn't match the results you gave. 103 has a description of ccc and yet you say you only want a description of aaa.

[mgdvicky]

I mean whatever itemcode I use in finperiod = 'jan-14' and also I use same itemcode in finperiod = 'feb-14'. Sometimes, I don't use same itemcode whatever I used in finperiod = 'jan-14'. I need the output for that unused itemcode. Ex: (103, 'jan-14', 'fy2014') this same itemcode I don't use for finperiod = 'feb-14'. So the output is : 103.

[Rabbit]

So if you're dropping the itemdesc criteria, then that makes more sense. When you had the data split into multiple tables, you probably used an outer join to find what you needed correct? You can do the same thing. Except you just join the table to itself and include the criteria in the join.

[mgdvicky]

That is my problem. I don't know how to write join query for this criteria.

[Rabbit]

It would be something like this:

SELECT ...
FROM
   tableName AS t1
 
   LEFT JOIN tableName AS t2
   ON t1.IDField = t2.IDField
   AND t2.ValueField = 'value you don't want'
 
WHERE
   t1.ValueField = 'value you do want' AND
   t2.IDField IS NULL