complex query problem

clientid employeeid
customers <---- jobcards ------> employees
| handedovertoid
__________________________|_______________________ | | |
jobcarddetails-->employees jobcardlabor-->employees jobcardparts-->parts

hi Guys,
above is the structure of the transaction tables:
jobcards, jobcarddetails,jobcardlabor,jobcardparts
where
customer,employees and parts are the master reference tables.
I want to retrieve the records from these tables in a single recordset, so i avoid roundtrips, the sql query is below. Problem with this query is that i want it to return the maximum rows from each of the child table, so i can iterate and fill through a single recordset. Problem area is the employee table which i referred by jobcards, jobcarddetails and jobcardlabor.
example : if 2 records in jobcarddetails then it return 2 rows with duplicating record information for jobcard master and other tables. but if there are 2 records in jobcardlabor then i want it to return 2 records in all with jobcard details and jobcardlabor having different record data.
I hope my requirment is clear. now if some1 can solve this query or just suggest an alternative.

SELECT JobCards.JobCardID, JobCards.CustomerID,
JobCards.EmployeeID, JobCards.PersonReporting,
employees.firstname + ' ' + employees.lastname as reportTakenbyName,
JobCards.NatureOfProblem, JobCards.HandedToID,
e1.firstname + ' ' + e1.lastname as HandedToName,
JobCards.InvoiceNumber, JobCards.DateReceived,
JobCards.DateRequired, JobCards.MakeAndModel,
JobCards.SerialNumber, JobCards.SoftwareProblem,
JobCards.DateFinished, JobCards.DatePickedUp,
JobCards.SalesTaxRate, JobCards.JobSite,
JobCards.Accessories, Customers.CompanyName,
JobCardDetails.Description, jobCardDetails.ActionTaken,
JobCardDetails.DateTakenUp,
JobCardDetails.DateFinished AS detailsDtFinished,
JobCardLabor.EmployeeID AS EmployeeLabourID,
JobCardLabor.BillableHours, JobCardLabor.BillingRate,
JobCardLabor.Comment, JobCardLabor.JobCardLaborID,
e3.firstname + ' ' + e3.lastname as JobCardLaborName,
JobCardParts.UnitPrice, JobCardParts.Quantity,
Parts.PartName, JobCardParts.JobCardPartID,
JobCardDetails.AttendedBy,
e2.firstname + ' ' + e2.lastname as AttendedByName
FROM jobcards, jobcarddetails, jobcardlabor, jobcardparts, employees, employees e1,
employees e2, employees e3,customers, parts
where jobcards.jobcardid = jobcarddetails.jobcardid and
jobcards.jobcardid = jobcardlabor.jobcardid and
jobcards.jobcardid = jobcardparts.jobcardid and
jobcards.employeeid = employees.employeeid and
jobcards.handedtoid = e1.employeeid and
jobcarddetails.attendedby = e2.employeeid and
jobcardlabor.employeeid = e3.employeeid and
jobcards.customerid = customers.customerid and
jobcardparts.partid = parts.partidPost DDL, test data and desired resultset to speed up the process.|||hi,
have attached the ddl script, test data in text document(csv) per table and desired result in a jpg file as well(which i generated using msdatashape)

hope it helps.

thanks.|||Try this FROM clause

FROM JobCards
LEFT JOIN JobCardDetails ON JobCards.JobCardID = JobCardDetails.JobCardID
LEFT JOIN JobCardLabor ON JobCards.JobCardID = JobCardLabor.JobCardID
LEFT JOIN JobCardParts ON JobCards.JobCardID = JobCardParts.JobCardID
LEFT JOIN Employees ON JobCards.EmployeeID = Employees.EmployeeID
LEFT JOIN Employees e1 ON JobCards.HandedToID = e1.EmployeeID
FULL JOIN Employees e2 ON JobCardDetails.AttendedBy = e2.EmployeeID
FULL JOIN Employees e3 ON JobCardLabor.EmployeeID = e3.EmployeeID
LEFT JOIN customers ON JobCards.CustomerID = customers.customerid
FULL JOIN Parts ON JobCardParts.PartID = Parts.PartID

I am not sure what outer joins are necessary. You can optimize.|||Doesnt quite give me the desired result.

if u use dataenvironment and create a Hierarchial recordset then it will show u the results which i m looking for.

coz if u add a single record in lets say parts then it gives 3 records more: 1 for jobcarddetails and two for jobcardlabor.

the full outer joins r not serving any purpose as i tried those as well.|||MSSQLOLEDB data provider cannot return a hieraset.|||yes i knw abt msoledb provider, thats the reason y this problem.

but no solution yet except that i have to use that hierach recordset.