7 Replies Latest reply: Nov 19, 2012 10:36 PM by 975100 RSS

    Double Check Statement

    975100
      I have two tables:

      CustomerContacts
      CustomerID (int)
      CustomerName (string)
      ContactingRepresentativeID (int, foreign key to the Employees table)
      ContactType (string) -- e.g. "phone", "email", etc
      ContactedDate (datetime)

      Employees
      ID (int, primary key)
      FullName (string)

      I have created the following query to match this requirement:

      All customers that have ever been contacted, the last time they have been contacted, who contacted them at that time and how they where contacted. Is this about right on?

      SELECT *
      FROM CustomerContacts as C
      INNER JOIN
      (
      SELECT ContactingRepresentativeID, CustomerContacts.ContactType, CustomerContacts.ContactType, Employee.FullName, max(ContactedDate) AS LastContact
      FROM CustomerContacts
      GROUP BY CustomerContacts.ContactingRepresentativeID
      ) AS RecentContact

      INNER JOIN on Employee as E
      on C.ContactingRepresentativeID = E.ID AND E.ContactedDate = RecentContact.LastContact
        • 1. Re: Double Check Statement
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Double Check Statement
            SomeoneElse
            I have two tables:
            It sure would have been a lot nicer if you posted real CREATE TABLE statements and some sample data instead of this abstract.
            • 3. Re: Double Check Statement
              rp0428
              >
              All customers that have ever been contacted, the last time they have been contacted, who contacted them at that time and how they where contacted
              >
              And what happened when you executed your query? You got errors, that's what happened. That should be a clue that your query has problems.

              Assuming that contacteddate is null if the customer has never been contacted (untested)
              with cust as (select customerId, customername, contactingrepresentativeid, contacttype, max(contacteddate) contacteddate
                  from customercontacts where contacteddate is not null
                  group by customerid, customername, contactingrepresentativeid, contacttype)
              select c.customerid, c.customername, c.contacttype, c.contacteddate, e.fullname
              from cust c, employees e
              where c.contactingrepresentativeid = e.id;
              You should be able to fix any syntax errors.

              The table expression ('WITH CUST ...') selects the customer record with the latest (maximum) contacteddate. Then you just need to join those records to the other table.
              • 4. Re: Double Check Statement
                975100
                CREATE TABLE Employees
                (
                E_IdD int PRIMARY KEY,
                FullName varchar(255)

                )

                CREATE TABLE CustomerContacts
                (
                C_Id int,
                CustomerName varchar(255),
                ContactingRepresentativeID int,
                ContactType varchar(255),
                ContactedDate datetime
                FOREIGN KEY (ContactingRepresentativeID) REFERENCES Employees(E_Id)

                )

                INSERT INTO CustomerContacts
                (CustomerName, ContactingRepresentativeID, ContactType, ContactedDate)
                VALUES
                ('brian', 'chad', 'jaun', 'fletch', 'yugo'),
                ('1', '2', '3', '4'),
                ('email', 'phone', 'email', 'email'),
                ('010112', '101012', '090912', '101010'),

                INSERT INTO Employees
                (E_ID, FullName)
                VALUES
                ('10', '20', '30', '40'),
                ('mike mike', 'bix bix', 'max max', 'jon jon');
                • 5. Re: Double Check Statement
                  SomeoneElse
                  These statements are full of errors, syntax and otherwise. Did you try to run them yourself?

                  You're making it more difficult for us to help you. Probably not what you want.
                  • 6. Re: Double Check Statement
                    975100
                    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
                    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
                    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

                    CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
                    USE `mydb` ;

                    -- -----------------------------------------------------
                    -- Table `mydb`.`Employees`
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS `mydb`.`Employees` (
                    `idEmployees` INT NOT NULL AUTO_INCREMENT ,
                    `FullName` VARCHAR(45) NULL ,
                    `Employeescol` VARCHAR(45) NULL ,
                    `Employeescol1` VARCHAR(45) NULL ,
                    `Employeescol2` VARCHAR(45) NULL ,
                    PRIMARY KEY (`idEmployees`) )
                    ENGINE = InnoDB;


                    -- -----------------------------------------------------
                    -- Table `mydb`.`CustomerContacts`
                    -- -----------------------------------------------------
                    CREATE TABLE IF NOT EXISTS `mydb`.`CustomerContacts` (
                    `idCustomerContacts` INT NOT NULL AUTO_INCREMENT ,
                    `CustomerName` VARCHAR(45) NULL ,
                    `ContactingRepresentativeID` VARCHAR(45) NULL ,
                    `ContactType` VARCHAR(45) NULL ,
                    `ContactDate` DATETIME NULL ,
                    INDEX `ContactingRepresentativeID_idx` (`idCustomerContacts` ASC) ,
                    CONSTRAINT `ContactingRepresentativeID`
                    FOREIGN KEY (`idCustomerContacts` )
                    REFERENCES `mydb`.`Employees` (`idEmployees` )
                    ON DELETE NO ACTION
                    ON UPDATE NO ACTION)
                    ENGINE = InnoDB;



                    SET SQL_MODE=@OLD_SQL_MODE;
                    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
                    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


                    /*
                    -- Query:
                    -- Date: 2012-11-19 22:32
                    */
                    INSERT INTO `Employees` (`idEmployees`,`FullName`) VALUES (1,'ape');
                    INSERT INTO `Employees` (`idEmployees`,`FullName`) VALUES (2,'bear');
                    INSERT INTO `Employees` (`idEmployees`,`FullName`) VALUES (3,'cat');
                    INSERT INTO `Employees` (`idEmployees`,`FullName`) VALUES (4,'dex');


                    /*
                    -- Query:
                    -- Date: 2012-11-19 22:35
                    */
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (20,'frank','1','Phone','010112');
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (30,'myra','2','Phone','010112');
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (40,'gold','3','Email','010110');
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (50,'suze','4','Email','010110');
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (60,'miyo','2','Email','010111');
                    INSERT INTO `CustomerContacts` (`idCustomerContacts`,`CustomerName`,`ContactingRepresentativeID`,`ContactType`,`ContactDate`) VALUES (70,'peter','4','Email','010109');
                    • 7. Re: Double Check Statement
                      jeneesh
                      Is yous ORACLE database?