6 Replies Latest reply: May 4, 2012 7:18 AM by mnp13 RSS

    Cross connection query, finding non-matching data

    mnp13
      Still on Oracle 11.2.0.1.0

      Sample data:

      In connectionA
      CREATE TABLE datasampleA (
               RID NUMBER(10) PRIMARY KEY,
               employeeNumber NUMBER(9),
                employeeName VARCHAR2(15),
               jobID VARCHAR2(10),
               job_date   DATE,
               active  NUMBER(9)
             )
      ;
       
      INSERT INTO datasampleA VALUES(121420,100171,'Jack',411,'03-JUN-99',1);
      INSERT INTO datasampleA VALUES(119590,100244,'Elizabeth',409,'13-SEP-99',1);
      INSERT INTO datasampleA VALUES(124252,100352,'Amy',7646,'25-MAY-08',1);
      INSERT INTO datasampleA VALUES(124253,100514,'Jonathan',642,'06-MAR-05',1);
      INSERT INTO datasampleA VALUES(116592,100526,'Ruben',642,'20-FEB-05',1);
      INSERT INTO datasampleA VALUES(116593,100526,'Ruben',7646,'20-FEB-05',0);
      INSERT INTO datasampleA VALUES(122273,100526,'Ruben',7646,'12-JAN-03',0);
      INSERT INTO datasampleA VALUES(121142,100615,'Peter',7646,'21-JUN-99',1);
      INSERT INTO datasampleA VALUES(116594,100822,'Kathee',642,'20-MAR-05',1);
      INSERT INTO datasampleA VALUES(119354,100890,'Christopher',7647,'28-AUG-11',0);
      INSERT INTO datasampleA VALUES(417079,100890,'Christopher',7646,'28-AUG-11',1);
      INSERT INTO datasampleA VALUES(122128,100946,'Christopher',411,'11-MAR-02',1);
      INSERT INTO datasampleA VALUES(118382,100963,'Kristi',8470,'01-AUG-10',0);
      INSERT INTO datasampleA VALUES(1252467,100963,'Kristi',8473,'01-AUG-10',1);
      INSERT INTO datasampleA VALUES(119355,100989,'Christopher',8473,'18-JUN-01',1);
      in connectionB
      CREATE TABLE datasampleB (
                RID NUMBER(10) PRIMARY KEY,
               employeeNumber NUMBER(9),
                employeeName VARCHAR2(15),
               jobID VARCHAR2(10),
               job_date   DATE,
               end_date DATE
             )
      ;
      
      INSERT INTO datasampleB VALUES(121420,100171,'Jack',415,'03-JUN-99','05-DEC-99');
      INSERT INTO datasampleB VALUES(119590,100244,'Elizabeth',413,'13-SEP-99','13-SEP-99');
      INSERT INTO datasampleB VALUES(124847,100244,'Elizabeth',642,'30-DEC-01',NULL);
      INSERT INTO datasampleB VALUES(124252,100352,'Amy',7650,'25-MAY-08','03-SEP-10');
      INSERT INTO datasampleB VALUES(124253,100514,'Jonathan',646,'06-MAR-05','05-JUN-05');
      INSERT INTO datasampleB VALUES(116592,100526,'Ruben',646,'20-FEB-05',NULL);
      INSERT INTO datasampleB VALUES(116593,100526,'Ruben',7650,'20-FEB-05','29-AUG-10');
      INSERT INTO datasampleB VALUES(122273,100526,'Ruben',7650,'12-JAN-03','29-AUG-10');
      INSERT INTO datasampleB VALUES(121142,100615,'Peter',411,'21-JUN-99',NULL);
      INSERT INTO datasampleB VALUES(116594,100822,'Kathee',646,'20-MAR-05','21-OCT-06');
      INSERT INTO datasampleB VALUES(119354,100890,'Christopher',7650,'28-AUG-11','28-AUG-11');
      INSERT INTO datasampleB VALUES(417079,100890,'Christopher',7646,'28-AUG-11',NULL);
      INSERT INTO datasampleB VALUES(122128,100946,'Christopher',415,'11-MAR-02','27-JUN-02');
      INSERT INTO datasampleB VALUES(118382,100963,'Kristi',8477,'01-AUG-10','01-NOV-11');
      INSERT INTO datasampleB VALUES(831596,100963,'Kristi',8473,'01-AUG-10',NULL);
      INSERT INTO datasampleB VALUES(860841,100963,'Kristi',8477,'01-AUG-10','01-MAR-12');
      INSERT INTO datasampleB VALUES(1252467,100963,'Kristi',8477,'01-AUG-10','01-MAY-12');
      INSERT INTO datasampleB VALUES(119355,100989,'Christopher',410,'18-JUN-01',NULL);
      INSERT INTO datasampleB VALUES(119356,101141,'Jared',416,'12-SEP-99','30-JUN-00');
      Ok, I've been working on this but I'm not getting the right results when I'm doing it. I'm trying to compare data from two different connections to only return the people with different numbers in "jobID"

      In English, this is what I want:
      All employees from A (where Active is 1), that are also in B (where end_date is NULL), where the JobID's don't match.

      So it should return:
      100244
      100526
      100615
      100989

      But I have not been able to figure out how to actually get that output.

      Thanks!

      Michelle
        • 1. Re: Cross connection query, finding non-matching data
          indra budiantho
          select *
          from datasampleA a, datasampleB b
          where a.employeeNumber  = b.employeeNumber  
          and a.jobID != b.jobID 
          • 2. Re: Cross connection query, finding non-matching data
            Igor.M
            select *
            from datasampleA a
            where active = 1 and exists (
                                                     select 1 from datasampleB b 
                                                     where b.employeeNumber = a.employeeNumber
                                                            and b.end_date is NULL
                                                            and  b.jobID  != a.jobID
                                                     );
            
                   RID EMPLOYEENUMBER EMPLOYEENAME    JOBID      JOB_DATE                  ACTIVE
            ---------- -------------- --------------- ---------- --------------------- ----------
                119590         100244 Elizabeth       409        13.09.0099 00:00:00            1
                116592         100526 Ruben           642        20.02.0005 00:00:00            1
                121142         100615 Peter           7646       21.06.0099 00:00:00            1
                119355         100989 Christopher     8473       18.06.0001 00:00:00            1
            • 3. Re: Cross connection query, finding non-matching data
              Paul  Horth
              Try
              select *
                from datasamplea a
               inner join datasampleb b
                  on a.employeenumber = b.employeenumber
               where a.active = 1
                 and b.end_date is null
                 and a.jobid != b.jobid;
              Edited by: Paul Horth on 03-May-2012 05:26
              • 4. Re: Cross connection query, finding non-matching data
                mnp13
                What we're missing is the different connections.

                One table is in databaseA, the other table is in databaseB

                I have active connections to both, and can query both from Oracle SQL Developer, but I can't figure out how to query both in the same SQL statement.
                • 5. Re: Cross connection query, finding non-matching data
                  Paul  Horth
                  You need to use a Database link. See:

                  http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505
                  • 6. Re: Cross connection query, finding non-matching data
                    mnp13
                    Ok, both versions that were posted worked based on the criteria that I posted.

                    However, it turns out that the person who needs my output for their work needs to show a column from table B in the output. That's not possible with the "exists" version of the query (or at least I couldn't figure out how to do it!)

                    So, though both work and the "exists" query works a bit faster, I went with the other one as the "correct" answer.

                    And the database link is setup and working, I couldn't actually do that myself because I don't have that level of permissions on the database. But it's all working now!

                    Thanks for your help!

                    Edited by: mnp13 on May 4, 2012 5:17 AM