By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,842 Members | 2,198 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,842 IT Pros & Developers. It's quick & easy.

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

P: 12
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...
Apr 20 '15 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,335
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.
Apr 20 '15 #2

P: 12
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.
Apr 21 '15 #3

Rabbit
Expert Mod 10K+
P: 12,335
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.
Apr 21 '15 #4

P: 12
That is my problem. I don't know how to write join query for this criteria.
Apr 21 '15 #5

Rabbit
Expert Mod 10K+
P: 12,335
It would be something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2. FROM
  3.    tableName AS t1
  4.  
  5.    LEFT JOIN tableName AS t2
  6.    ON t1.IDField = t2.IDField
  7.    AND t2.ValueField = 'value you don't want'
  8.  
  9. WHERE
  10.    t1.ValueField = 'value you do want' AND
  11.    t2.IDField IS NULL
Apr 21 '15 #6

Post your reply

Sign in to post your reply or Sign up for a free account.