Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Smile Unanswered: SQL Help - Duplicated Records

    I am trying to select the order numbers (OrdNo) from the order table (OrdTbl) where the employee number (EmpNo) exists in the employee table (Employee) and the order date (OrdDate) is Jan 23 00 ~

    It is supposed to return 4 rows that has OrdDate Jan 23 00, but it gives me 6 x 4 = 24 rows because there are 6 employees in the employee table If I do NOT select Employee table in my outer statment, it is fine ~

    Code:
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.EmpFirstName FROM OrderTbl, Employee WHERE 
    	OrderTbl.OrdDate = '23-JAN-00'
    	AND OrderTbl.EmpNo IN (
    		SELECT Employee.EmpNo FROM Employee
    	);
    );
    You can see there are duplicated order numbers
    [ADDED]I think I am missing the group by statement ... but I don't know where to put it ~

    Code:
    ORDNO    ORDDATE   EMPFIRSTNAME
    -------- --------- --------------------
    O1116324 23-JAN-00 Landi
    O1231231 23-JAN-00 Landi
    O1615141 23-JAN-00 Landi
    O3252629 23-JAN-00 Landi
    O1116324 23-JAN-00 Joe
    O1231231 23-JAN-00 Joe
    O1615141 23-JAN-00 Joe
    O3252629 23-JAN-00 Joe
    O1116324 23-JAN-00 Amy
    O1231231 23-JAN-00 Amy
    O1615141 23-JAN-00 Amy
    O3252629 23-JAN-00 Amy
    O1116324 23-JAN-00 Colin
    O1231231 23-JAN-00 Colin
    O1615141 23-JAN-00 Colin
    O3252629 23-JAN-00 Colin
    O1116324 23-JAN-00 Thomas
    O1231231 23-JAN-00 Thomas
    O1615141 23-JAN-00 Thomas
    O3252629 23-JAN-00 Thomas
    O1116324 23-JAN-00 Mary
    O1231231 23-JAN-00 Mary
    O1615141 23-JAN-00 Mary
    O3252629 23-JAN-00 Mary
    Expected Result
    Code:
    ORDNO    ORDDATE  EMPFIRSTNAME 
    -------- --------- ------------ 
    O1116324 23-JAN-04 Joe
    O1231231 23-JAN-04 Mary
    O1615141 23-JAN-04 Joe
    O3252629 23-JAN-04 Mary
    Last edited by YUPAPA; 11-25-04 at 03:15.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Unless I got it wrong, the problem is that your query misse a where clause joining OrderTbl and Employee, causing it to return too many rows.


    Try this :
    a) skip the IN clause
    b) complete your first select

    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.EmpFirstName FROM
    OrderTbl, Employee
    WHERE
    OrderTbl.Empno = Employee.EmpNo
    AND
    OrderTbl.OrdDate = '23-JAN-00'
    ;

    CVM
    Last edited by cvandemaele; 11-25-04 at 04:21. Reason: missed AND

  3. #3
    Join Date
    Mar 2004
    Posts
    17
    Hi,

    That does it~

    But the question is complicated than what I have just posted earlier. I also want to list the employee that is not assoicated in the OrderTbl.

    The query above works when the employee no exists in both tables ~

    Here is the query I come up with, but it doesn't work ~

    Code:
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.EmpFirstName
    FROM
    OrderTbl, Employee
    WHERE
    (OrderTbl.Empno = Employee.EmpNo OR OrderTbl.EmpNo IS NULL)
    AND
    OrderTbl.OrdDate = '23-JAN-00'
    Last edited by YUPAPA; 11-25-04 at 14:57.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    If I understand your question, you need to return an additional row for each employee that is not referenced in the ordertable as of 23.01.2000

    FIRST SOLUTION
    You keep the first select, and you append the missing data using the UNION ALL clause.

    SECOND SOLUTION
    You modify the first select so to make it a RIGHT OUTER JOIN.

    Here it goes :

    TEST DATA
    =========
    create table OrderTbl (Ordno int, orddate date, empno int);
    create table Employee (empno int, empfirstname varchar2(10), emplastname varchar2(30));

    insert into employee values (1, 'John', 'Doe');
    insert into employee values (2, 'Mark', 'Spencer');
    insert into employee values (3, 'Igor', 'Stravinsky');
    insert into employee values (4, 'Mike', 'Mullan');

    insert into ordertbl values (1, to_date('20000123','yyyymmdd'), 1);
    insert into ordertbl values (2, to_date('20000123','yyyymmdd'), 1);
    insert into ordertbl values (3, to_date('20000123','yyyymmdd'), 2);
    insert into ordertbl values (4, to_date('20000124','yyyymmdd'), 3);
    insert into ordertbl values (5, to_date('20000124','yyyymmdd'), 1);

    USING UNION ALL
    =============
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.empno, Employee.EmpFirstName
    FROM OrderTbl, Employee
    WHERE OrderTbl.Empno = Employee.EmpNo
    AND OrderTbl.OrdDate = to_date('20000123','yyyymmdd')
    UNION ALL
    select null, null, employee.empno, employee.EmpFirstName
    from employee
    where employee.empno not in (select empno from ordertbl where orddate = to_date('20000123','yyyymmdd'));

    returns

    ORDNO ORDDATE EMPNO EMPFIRSTNAME
    ---------- ----------- ---------- ------------
    1 23.01.2000 1 John
    2 23.01.2000 1 John
    3 23.01.2000 2 Mark
    3 Igor
    4 Mike

    USING RIGHT OUTER JOIN
    ===================
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, employee.empno, Employee.EmpFirstName
    FROM
    Ordertbl right outer join employee
    on OrderTbl.Empno = Employee.EmpNo and OrderTbl.orddate = to_date('20000123','yyyymmdd');

    returns the same data, of course.

  5. #5
    Join Date
    Mar 2004
    Posts
    17

    Smile

    Thanks for your help so much~ We are almost there~

    Here is what I am trying to do:
    I am trying to list the order number, order date and the employee number of orders placed on Jan 23 00. Also list the order even if there is not an assoicated employee in the order table.

    TABLE: OrderTbl
    FIELDS: OrdNo, OrdDate, OrdEmp

    TABLE: Employee
    FIELDS: EmpNo, EmpName

    The OrdEmp is related to EmpNo

    So it would be:
    create table OrderTbl (Ordno int, orddate date, empno int);
    create table Employee (empno int, empfirstname varchar2(10), emplastname varchar2(30));

    insert into employee values (1, 'John', 'Doe');
    insert into employee values (2, 'Mark', 'Spencer');
    insert into employee values (3, 'Igor', 'Stravinsky');
    insert into employee values (4, 'Mike', 'Mullan');

    insert into ordertbl values (1, to_date('20000123','yyyymmdd'), 1);
    insert into ordertbl values (2, to_date('20000123','yyyymmdd'), 1);
    insert into ordertbl values (3, to_date('20000123','yyyymmdd'), NULL);
    insert into ordertbl values (4, to_date('20000124','yyyymmdd'), 3);
    insert into ordertbl values (5, to_date('20000124','yyyymmdd'), 1);

    The result should return:
    Code:
    OrdNo, OrdDate, EmpNo
    -----------------------
    1  JAN-13-2000  1
    2  JAN-13-2000  1
    3  JAN-13-2000

    I attempt to make the SQL using the info you gave me, but still no success.
    Not sure you understand what my SQL statement trying to do ~ If it is too confusing, please ignore the SQL I made ~

    Code:
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.empno, Employee.EmpFirstName
    FROM OrderTbl, Employee
    WHERE OrderTbl.Empno = Employee.EmpNo
    AND OrderTbl.OrdDate = to_date('20000123','yyyymmdd')
    UNION ALL
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, null, null
    FROM OrderTbl, Employee
    WHERE OrderTbl.OrdNo IN (SELECT OrdNo from OrderTbl where OrdDate = to_date('20000123','yyyymmdd') AND OrderTbl.EmpNo IS NULL);
    Thanks!!
    Last edited by YUPAPA; 11-25-04 at 19:26.

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Ok so I did not understand your question the first time, should be much easier now you provided the expected result.
    What you need is a left outer join, meaning that all rows from ordertbl (more exactly those matching 23.01.2000) are returned regardless of their match in the employee table.

    Code:
     
    SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, Employee.empno, Employee.EmpFirstName
    FROM OrderTbl
    left outer join Employee
    on employee.empno = ordertbl.empno
    where ordertbl.orddate = to_date('23/01/2000','dd/mm/yyyy')

  7. #7
    Join Date
    Mar 2004
    Posts
    17

    Smile

    Thanks!!

Posting Permissions

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