Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    Unanswered: Easy query help... :(


    I'm trying to write a sql query to create a report that will act kind of like a Mail Merge in Microsoft Word. I want to send letters to our customers to remind them their machines need to be ballbar'd or calibrated.

    My database is set up as follows:

    CustomerInformation(CustomerID[pk], CompanyName, CompanyPhone, etc etc etc)

    CustomerMachine(MachineID[pk], MachineManufacturer, MachineModel, MachineSerialNumber, MachineSize, CustomerID[fk])

    LaserCalibrations(LaserID[pk], LaserDate, MachineID[fk])

    Ballbars(BallbarID[pk], BallbarDate, MachineID[fk])

    My query looks like this:

    SELECT CustomerInformation.CustomerID, CustomerMachine.CustomerID, CustomerMachine.MachineID, CustomerMachine.MachineManufacturer, CustomerMachine.MachineModel, CustomerMachine.MachineSerialNumber, CustomerMachine.MachineSize, LaserCalibrations.LaserID, LaserCalibrations.LaserDate, LaserCalibrations.MachineID

    FROM CustomerInformation, CustomerMachine, LaserCalibrations

    WHERE (((CustomerInformation.CustomerID)=CustomerMachine .CustomerID) And ((CustomerMachine.MachineID)=LaserCalibrations.Mac hineID))
    ORDER BY CustomerInformation.CompanyName, LaserCalibrations.LaserDate;

    The problem is that it will not print the laser calibration dates in order. I wanted to include a history of past service on the letter. I tried to write the query including ballbars, but it got too messy, I think I was missing a "Distinct" descripter. I also want to change the price according to the size of the machine... and I'm not certain how to go about doing that.

    Please help


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you have here two unrelated one-to-many relationships that cannot easily be combined in one query

    if a single machine has 3 LaserCalibrations and 4 Ballbars, the query will return 12 rows for that machine

    you need two separate queries, one for LaserCalibrations and a separate one for Ballbars | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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