Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26

    Wink Unanswered: complex query problem

    Code:
                         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.


    Code:
    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.partid

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Post DDL, test data and desired resultset to speed up the process.

  3. #3
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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.

  5. #5
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    MSSQLOLEDB data provider cannot return a hieraset.

  7. #7
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •