Query for fetching matching data in two columns

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Hi All,

I am trying to write a query for a scenario in which i need to fetch those Names from Employee table which are having same Dept and Salary. The structure of Employee table as follows :

Name Dept Salary

AAA HR 1000
BBB HR 2000
CCC IT 3000
DDD IT 3000
EEE FIN 5000
FFF FIN 5000

Result table should be like this :

CCC IT 3000
DDD IT 3000

This is the query which i have wrote :

SELECT Name FROM Employee WHERE row(Dept,Salary) IN (SELECT Dept, Salary FROM Employee GROUP BY Dept,Salary HAVING COUNT(*)>1);

But this query is giving all the rows because the Inner query selecting all 3 unique Dept & Salary.

Can anyone please let me know where i need to make the changes in the query so i can get the expected result.

Rohit Umarjikar · Posted: Mon Jul 09, 2018 6:29 pm

Tell us why last two rows wouldn’t make it in the result ?

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Sorry Rohit. It was a typo. Please find the correct structure of Employee table below :

Name Dept Salary

AAA HR 1000
BBB HR 2000
CCC IT 3000
DDD IT 3000
EEE FIN 5000
FFF FIN 6000

Result table should be like this :

CCC IT 3000
DDD IT 3000

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

How are you running the ROW function in DB2? I don't know if it even exists for DB2. You should get an error.

ROW function is, as per my knowledge, for other DBMS such as MySQL.

.

Rohit Umarjikar · Posted: Mon Jul 09, 2018 9:24 pm

RahulG31 is correct.
Poha Eater, Isn't it very simple to achieve what you asked, please search around on internet/this forum , hint is to use either sub query or EXIST .

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Hi All,

I made the slight changes in my above query and its working now. Below is the final query :

SELECT Name, Dept, Salary FROM Employee WHERE (Dept,Salary) IN (SELECT Dept, Salary FROM Employee GROUP BY Dept,Salary HAVING COUNT(*)>1);

Thanks All for your support !!

Rohit Umarjikar · Posted: Tue Jul 10, 2018 5:07 pm

Good, EXISTS Is better to use it in such situations.

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Hi Rohit,

I was curious so wanted to ask if there is any specific difference between EXISTS and IN which can lead to performance improvement if the table size is huge ?

Rohit Umarjikar · Posted: Wed Jul 11, 2018 3:03 am

The Exists keyword evaluates true or false, but IN keyword compare all value in the corresponding sub query column. So the result data of the subquery needs to be loaded first to get compared against outer query regardless of matches

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Got it. Thanks again Rohit

Rohit Umarjikar · Posted: Wed Jul 11, 2018 9:16 pm

If you are executing the above query in online and the below table only gets updated in batch then I would suggest to create a new table and insert to it only when below condition is met and use that table in online as a inner join. This approach will avoid group by every time you hit the sp.