SQL Query Help

[casstd]

Hi,

I have jobs, jobapprover table. Job submitter can be a approver for another job. i.e one to many relationship with these fields

JobID SubmitterEmail JobName
1 aaa@company.com job1
2 bbb@company.com job2
3 abc@company.com job3
4 cdc@company.com job4

2nd table jobapprover
JobAssignedid jobid approveremail
1 1 approver1@compa ny.com
2 2 aaa@company.com
3 1 approver2@compa ny.com
4 4 aaa@company.com

I want the result set i.e i will be passing email address
ex: aaa@company.com email i want the record he submitted and the job assigned to him in a single query
JobID JobName Email
1 job1 aaa@company.com
2 job2 aaa@company.com
4 job4 aaa@company.com

[Delerna]

not sure if this is what you are after
but you could call a stored procedure like this

     exec prListJobs 'aaa@company.com'

where the stored procedure is something like this

CREATE PROC prListJobs @UserAddress varchar(100)
as
    SELECT jobID,JobName,SubmitterEmail as Email
    FROM tblSubmittedJobs
    WHERE SubmitterEmail = @UserAddress 
    UNION ALL
    SELECT a.jobID,a.JobName,b.ApproverEmail as Email
    FROM tblSubmittedJobs a
    LEFT JOIN tblApprovedJobs b on a.jobID=b.jobID
    WHERE ApproverEmail=@UserAddress 
GO

[casstd]

Hi,

Thank you for your reply.
I have given example. i have more columns to select from Job tables than job assigned table.

Thank you.

not sure if this is what you are after
but you could call a stored procedure like this

     exec prListJobs 'aaa@company.com'

where the stored procedure is something like this

CREATE PROC prListJobs @UserAddress varchar(100)
as
    SELECT jobID,JobName,SubmitterEmail as Email
    FROM tblSubmittedJobs
    WHERE SubmitterEmail = @UserAddress 
    UNION ALL
    SELECT a.jobID,a.JobName,b.ApproverEmail as Email
    FROM tblSubmittedJobs a
    LEFT JOIN tblApprovedJobs b on a.jobID=b.jobID
    WHERE ApproverEmail=@UserAddress 
GO