asked on

SQL script help

I have 2 tables:

projmatl (master table)
job (detail table)

They have a 1:1 relationship in most cases. In some case there is no record in the job table that will join back projmatl. They should join on the following fields:

(projmatl.proj_num)= (Job.ord_num)
(projmatl.item)= (Job.Item)
(projmatl.task_num)= (Job.ord_line)
(projmatl.seq)= (Job.ord_release)

My resultset should be the total number of records and fields from projmatl and the job.job field if there is a record that joins back to the master table.

What would the sql be?

elimesika

select * from projmatl, job
where
(projmatl.proj_num)= (Job.ord_num)
(projmatl.item)= (Job.Item)
(projmatl.task_num)= (Job.ord_line)
(projmatl.seq)= (Job.ord_release)

Open in new window

elimesika

Sorry
Take that

select * from projmatl, job
where
projmatl.proj_num= Job.ord_num and 
projmatl.item= Job.Item and
projmatl.task_num= Job.ord_line and
projmatl.seq)= Job.ord_release

Open in new window

tigin44

SELECT projmatl.*, Job.job
FROM projmatl
      INNER JOIN Job ON projmatl.proj_num= Job.ord_num
                        AND projmatl.item = Job.Item
                        AND projmatl.task_num = Job.ord_line
                        AND projmatl.seq = Job.ord_release

thenrich

ASKER

tigin44,
The SQL in constructed in close but it did not return a full result set. I tested this by suppling a parameter to the field projmatl.proj_num. I made it an actual value of 'AI00001204'.
When I do a select * from projmatl where projmatl.proj_num='AI00001204' I get 1060 records. When I do Select * From job where job.ord_num='AI00001204' I get 161 records - which is what your query returns. Please bare in mind that projmatl is the master table and there will not always be a join back to the job table.

elimesika:
no luck with either suggestion

tigin44

SELECT projmatl.*, Job.job
FROM projmatl
LEFT OUTER JOIN Job ON projmatl.proj_num= Job.ord_num
AND projmatl.item = Job.Item
AND projmatl.task_num = Job.ord_line
AND projmatl.seq = Job.ord_release

thenrich

ASKER

now I have returned 48,715 rows in my resultset - way to many.

thenrich

ASKER

When I run the query below and return the 48,715 records I have values in the pro_num field that are NOT 'AI00001204'. That does not make sense to me.

SELECT projmatl.proj_num, Job.job
FROM projmatl
LEFT OUTER JOIN Job ON projmatl.proj_num= Job.ord_num
AND projmatl.item = Job.Item
AND projmatl.task_num = Job.ord_line
AND projmatl.seq = Job.ord_release
AND projmatl.proj_num = 'AI00001204'

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thenrich

ASKER

yup - you got it. I knew if I kept giving you more detail you'd figure it out.