10 Replies Latest reply on May 24, 2010 2:48 PM by 741535

    Complex sql query

    741535
      table a and table b

      Table 1
      empid (pk)
      empr number
      year

      Table 2
      detail_id(pk)
      empid(fk)
      ssn
      wages


      I have 2 tables parent(TABLE 1) and a child (TABLE 2)

      i want to write a sql query to get the records from TABLE 2 WHICH HAVE THE SSN AND WAGES. using a condition which is below

      all i know is the empr number and year, using this i need to get all the records from Table1 which has the same empr number and year, and then for those records in table 2(emp id) i need to get the duplicates (which have the same ssn and wages)


      does anyone know how to write a query for that

      if you did not understand please ask, thanks a lot for trying

      thanks
        • 1. Re: Complex sql query
          SomeoneElse
          Whenever asking a question like this, it always helps us when you post CREATE TABLE and INSERT statements with sample data. Something we can easily cut/paste into a SQL Plus session.

          CREATE TABLE employees
          (empid number primary key
          ,hiredate date
          ...

          INSERT INTO employees VALUES (.......);
          • 2. Re: Complex sql query
            Frank Kulash
            Hi,

            You probably want something like this:
            SELECT  t2.*
            FROM     table_1     t1
            JOIN     table_2     t2     ON     ta.empid     = t2.empid
            WHERE     t1.empr     = :p_empr
            AND     t1.year     = :p_year
            ;
            but I'm not sure I understand the problem, and had to guess at some things.

            If you want to, you can hard-code literals in place of the bind variables :p_empr and :p_year.

            Whenever you have a problem, please post CREATE TABLE and INSERT statements for a little sample data.
            Also post the results you want from that data, and an explanation (with specific examples from the data you posted) of how to get those results.
            • 3. Re: Complex sql query
              741535
              CREATE TABLE EMPLOYER
              (
              EMPR_ ID (pk) NUMBER(12),
              EMPR_NBR VARCHAR2(8 BYTE),
              Year VARCHAR2(5 BYTE),

              )
              INSERT INTO EMPLOYER VALUES (100,1234567,2009);


              CREATE TABLE EMPLOYEE1
              (
              EMPR_ID (FK) NUMBER(12),
              EMPL_ID (PK) NUMBER(16),
              SSN VARCHAR2(9 BYTE),
              WGE NUMBER(10,2),

              )
              INSERT INTO EMPLOYEE VALUES (100,1,123123122,1232.00);
              • 4. Re: Complex sql query
                Etbin
                Perhaps
                select detail_id,empid,ssn,wages
                  from (select detail_id,empid,ssn,wages,
                               count(distinct to_char(ssn) || '|' || to_char(wages)) over (partition by empid) dups
                          from table_2
                         where empid in (select empid
                                           from table_1
                                          where empr_number = :emp_number
                                            and year = :year
                                        )
                       )
                 where dups > 1
                 order by empid,ssn,wages
                Regards

                Etbin
                • 5. Re: Complex sql query
                  741535
                  thanks i will do that, you understood my problem right, now on the query you gave me i want to find duplicates (with same ssn and wages)

                  thanks a lot
                  • 6. Re: Complex sql query
                    SomeoneElse
                    SQL> CREATE TABLE EMPLOYER
                      2  (
                      3  EMPR_ ID (pk) NUMBER(12),
                      4  EMPR_NBR VARCHAR2(8 BYTE),
                      5  Year VARCHAR2(5 BYTE),
                      6  );
                    EMPR_ ID (pk) NUMBER(12),
                             *
                    ERROR at line 3:
                    ORA-00907: missing right parenthesis
                    
                    
                    SQL>
                    SQL> INSERT INTO EMPLOYER VALUES (100,1234567,2009);
                    INSERT INTO EMPLOYER VALUES (100,1234567,2009)
                                *
                    ERROR at line 1:
                    ORA-00942: table or view does not exist
                    
                    
                    SQL>
                    SQL> CREATE TABLE EMPLOYEE1
                      2  (
                      3  EMPR_ID (FK) NUMBER(12),
                      4  EMPL_ID (PK) NUMBER(16),
                      5  SSN VARCHAR2(9 BYTE),
                      6  WGE NUMBER(10,2),
                      7  );
                    EMPR_ID (FK) NUMBER(12),
                            *
                    ERROR at line 3:
                    ORA-00902: invalid datatype
                    • 7. Re: Complex sql query
                      Sudhakar_B
                      @OP,

                      As SomeoneElse pointed out... It is important and helpful if you post "create table and insert stamtments" that work and can be cut and pasted.

                      Most inportant in learning Oracle is to feel comfortable creating sample tables and loading samples data...
                      because, what we are all trying to MANAGE using Oracle is: The Data!.

                      Please take time to learn construct and syntax and take time to provide "correct and executable statement".
                      That way forum memebrs can spend their valuable time to solving actual problem instead of guessing and
                      trying to interpret "the problem".
                      Something we can easily cut/paste into a SQL Plus session
                      For example, Correct syntax and code that works...
                      CREATE TABLE EMPLOYER
                          (
                          EMPR_ID NUMBER(12) primary key ,
                          EMPR_NBR VARCHAR2(8 BYTE),
                          Year_id VARCHAR2(5 BYTE)
                         )
                      ;
                      
                      INSERT INTO EMPLOYER VALUES (100,1234567,2009)
                      ;
                      CREATE TABLE EMPLOYEE1
                      (
                      EMPR_ID  NUMBER(12) references employer (EMPR_ID ),
                      EMPL_ID  NUMBER(16) primary key,
                      SSN VARCHAR2(9 ),
                      WGE NUMBER(10,2)
                      )
                      ;
                      INSERT INTO EMPLOYEE1 VALUES (100,1,123123122,1232.00)
                      ;
                      Since Frank has provided a query I am confident it will work. Please TAKE time to understand the constructs and learn.
                      vr,
                      Sudhakar B.

                      Edited by: Sudhakar_B on May 23, 2010 6:19 PM
                      • 8. Re: Complex sql query
                        741535
                        Hi Frank,

                        the query you gave me works, the only thing i want to add is , i want this set of data to contain only duplicates, which is the combination of ssn and wage



                        SELECT t2.*
                        FROM     table_1     t1
                        JOIN     table_2     t2     ON     ta.empid     = t2.empid
                        WHERE     t1.empr     = :p_empr
                        AND     t1.year     = :p_year
                        ;


                        thanks a lot Frank, you are of great help
                        • 9. Re: Complex sql query
                          Frank Kulash
                          Hi,
                          oraclebeginner1231 wrote:
                          Hi Frank,

                          the query you gave me works, the only thing i want to add is , i want this set of data to contain only duplicates, which is the combination of ssn and wage
                          Sorry, I don't understand what you want.
                          Please post the results you want.

                          The sample data is what you posted yesterday had some syntax errors. Always test (and, if necessary, correct) your code before posting it.
                          I think you meant:
                          CREATE TABLE EMPLOYER
                          (
                          EMPR_ ID NUMBER(12),
                          EMPR_NBR VARCHAR2(8 BYTE),
                          Year VARCHAR2(5 BYTE)
                          );
                          
                          INSERT INTO EMPLOYER VALUES (100,1234567,2009);
                          
                          CREATE TABLE EMPLOYEE1
                          (
                          EMPR_ID NUMBER(12),
                          EMPL_ID NUMBER(16),
                          SSN VARCHAR2(9 BYTE),
                          WGE NUMBER(10,2)
                          );
                          
                          INSERT INTO EMPLOYEE VALUES (100,1,123123122,1232.00);
                          If this is the sample data, then what is a "duplicate"?
                          If the sample data is something else, what is it?
                          • 10. Re: Complex sql query
                            741535
                            sure i will do that, i got the solution for my problem...thanks