Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL query

PRDEEP
Occasional Contributor

PL/SQL query

Dear all,

I have two tables JOb_master and
Job_allocation.
Job_master columns
JOB_NO
SECTION
ISSUE_DATE

Job_alloction columns

JOB_NO
ALLOCATION_DT
SURVEYOR

Using the two tables I need the stament like
Surveyor,job_no,section

for this I wrote the query like
select surveyor,a.job_no,section from job_master a,job_allocation b where a.job_no in (select job_no from job_allocation) and a.job_no=b.job_no;

but it return duplicate rows,as per howmany times the job_no in job_allocation table.
could you plese find a solution.

Regards,
Pradeep

5 REPLIES
T G Manikandan
Honored Contributor

Re: PL/SQL query

select a.SECTION,a.JOB_NO,b.SURVEYOR from JOB_MASTER a, JOB_ALLOCATION b where a.JOB_NO=b.JOB_NO;

Re: PL/SQL query

Hi Pradeep,

Because the the JOB_NO is unique on Job_master (I believe it is the primary key on master table) and multi rows for each JOB_NO on Job_allocation, you will get duplicate rows definitly.

use the following:
select distinct surveyor,a.job_no,section from job_master a,job_allocation b where a.job_no=b.job_no;

Regards,
Xiaoang
Sajjad Sahir
Honored Contributor

Re: PL/SQL query

Dear Pradeep

select surveyor,a.job_no,section from job_master a,job_allocation b where
a.job_no=b.job_no;

I think this query is sufficient
one is primary key and second one is foriegn
key both are related through constraint mechanism

thanks and regards

Sajjad Sahir
PRDEEP
Occasional Contributor

Re: PL/SQL query

Thanks all of you,
but I got the same result as before,
and using distinct it return only one row.
For your reference I give the data as follows.
In Job Master table data as follows:-
Job_no Section Issue_date

5450 A 31-AUG-08
5450 B 31-AUG-08
5450 P 04-SEP-08
5450 Y 04-NOV-08
5450 Y 26-NOV-08
5450 A 30-NOV-08
5450 P 10-NOV-08

Job_allocation table

Job_no Allocation _date Surveyor

5450 04-NOV-08 1233445
5450 26-NOV-08 1233445

I need the result as follows

Surveyor Job_no Section

1233445 5450 Y
1233445 5450 Y

while run the following query
select b.SURVEYOR,a.JOB_NO, a.SECTION from JOB_MASTER a, JOB_ALLOCATION b where a.JOB_NO=b.JOB_NO and section='Y';

It give wrong result or duplicate rows.

Frank de Vries
Respected Contributor

Re: PL/SQL query

You can always take the duplicates out, by using the 'shadow' column rowid

eg.

create table owner.master_new
as
select * from owner.master x1
where x1.rowid <> (select max(x2.rowid) from history x2
where x1.ci_item = x2.ci_item)

If you want it to transpose to your concrete situation let us know
Look before you leap