Need help in Sub query

jwell rymbei · New User Joined: 14 Dec 2005 Posts: 22 Location: India

Hi,
I have two tables Emp and Empsal
Data in the tables are of like below:

Emp
=================================
Emp_Code Emp_name Emp_Join_Date lEmp_Join_Sa
a1234 XYZ 1998-01-01 150.00
b1234 XXZ 2000-01-01 120.00
c1234 YYZ 2000-01-01 450.00

Empsal
=================================
Emp_Code Emp_Sal Emp_Join_Date
a1234 100.00 1998-01-01
b1234 200.00 2000-01-01
c1234 900.00 2000-01-01

Now I need a list of employees joind in 2000 (Emp_join_date) where Emp_join_sal is less than Emp_sal.

nuthan · Posted: Fri Feb 10, 2006 12:40 pm

try this query
select emp_code a, emp_name a from emp a, empsal b where year(emp_join_date) = 2000 and a.emp_join_sal < b.emp_sal.

jwell rymbei · New User Joined: 14 Dec 2005 Posts: 22 Location: India

Hi Nuthan,
since emp_code is present in both the table it might give sql error. I can see select emp_code a, emp_name a from emp a, empsal b what is this a and b??

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 103

Hi,

It is a.emp_code and a.emp_name from emp a, empsal b. Here a nad b are alias names.

T & R
Prabs

nuthan · Posted: Fri Feb 10, 2006 3:13 pm

Hi Prabs,
Thanks for correcting me. its a.emp_code and a.emp_name only.

priyesh.agrawal · Posted: Fri Feb 10, 2006 3:16 pm

Prabs is correct...Here is modified query from Nuthan...

nikyojin · New User Joined: 05 Oct 2005 Posts: 94

Hi,
Modification for performance Tuning can be made..the join is also missing...the date functions in DB2 usually consume a lot of DB2 time...try to avoid it in queries.
Modified Query

jwell rymbei · New User Joined: 14 Dec 2005 Posts: 22 Location: India

Thanks guyz for all the valuable suggestions..
Now again the lets say data in two tables are like below
Emp
=================================
Emp_Code Emp_name Emp_Join_Date lEmp_Join_Sa
a1234 XYZ 1998-01-01 150.00
b1234 XXZ 2000-01-01 120.00
c1234 YYZ 2000-01-01 450.00

Empsal
=================================
Emp_Code Seq_no Emp_Sal Emp_Join_Date
a1234 001 100.00 1998-01-01
b1234 001 200.00 2000-01-01
b1234 002 150.00 2001-01-01
b1234 003 150.00 2001-01-01
c1234 900.00 2000-01-01

Now to list employees joind in 2000 (Emp_join_date) where Emp_join_sal is less than Emp_sal. What we need to do.
Note in Empsal table we have seq no added as a new column and for a emp_code we can have multiple records.

priyesh.agrawal · Posted: Fri Feb 10, 2006 8:51 pm

You would need to append this field in SELECT clause if you want to have it in the final output (which you will ofcourse, as there are multiple SEQ NO. for a EMP_CODE).

And that would be coded as B.SEQ_NO

Regards,

Priyesh.