Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2016
    Posts
    7

    Unanswered: Inner join, need to return all data in one row

    Hello, this is my first post .

    I am not an expert in SQL queries , so I need help with this .

    I have two tables , Table A and Table B.
    Each row in Table A , refers to two rows in table B.

    In my query , I need to return two columns of Table A and only one column on Table B.

    The problem I have is that when I make the join, returns two rows and I need all data in one row.

    For example:

    Table A: id, name, age

    1 JOHN 23

    Table B: id , ID_A , car

    1 1 BMW
    2 1 FORD

    Therefore , when I do the following query:

    select a.name , a.age , b.car
    from A a inner join B b on ( a.id = b.id_A )

    I get the following:

    JOHN 23 BMW
    JOHN 23 FORD

    But I need to return the following:

    JOHN 23 BMW FORD

    I already tried various things like pivot but got no results.

    Please could you help me with this?

    From already thank you very much.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Here are two options you may use; the first one is supported from 11g onwards (unless you create your own LISTAGG function; there's a script available; search for it if necessary).

    Code:
    SQL> WITH tab_a AS (SELECT 1 id, 'JOHN' name, 23 age FROM DUAL),
      2       tab_b
      3       AS (SELECT 1 id, 1 id_a, 'BMW' car FROM DUAL
      4           UNION
      5           SELECT 2, 1, 'FORD' FROM DUAL)
      6  SELECT a.name, a.age, LISTAGG (b.car, ', ') WITHIN GROUP (ORDER BY b.id) cars
      7    FROM tab_a a, tab_b b
      8   WHERE a.id = b.id_a;
    
    NAME        AGE CARS
    ---- ---------- --------------------
    JOHN         23 BMW, FORD
    
    SQL>
    SQL>
    SQL> WITH tab_a AS (SELECT 1 id, 'JOHN' name, 23 age FROM DUAL),
      2       tab_b
      3       AS (SELECT 1 id, 1 id_a, 'BMW' car FROM DUAL
      4           UNION
      5           SELECT 2, 1, 'FORD' FROM DUAL)
      6  SELECT a.name,
      7         a.age,
      8         RTRIM (
      9            XMLAGG (XMLELEMENT (e, b.car || ', ') ORDER BY b.id).EXTRACT (
     10               '//text()'),
     11            ', ')
     12            cars
     13    FROM tab_a a, tab_b b
     14   WHERE a.id = b.id_a;
    
    NAME        AGE CARS
    ---- ---------- --------------------
    JOHN         23 BMW, FORD
    
    SQL>

  3. #3
    Join Date
    Apr 2016
    Posts
    7
    Thank you very much for your help.

    Now , I have another question.

    Happening to another similar query that I have , I use the LISTAGG but to group .

    For example , I have this query:

    SELECT
    to_char(a.START,'DD-MON-YYYY'),
    to_char(a.END,'DD-MON-YYYY'),
    cast(a.stu as char(10)),
    COUNT(*),
    b.value
    FROM tableA a INNER JOIN tableB b on a.id = b.ID_A
    GROUP BY
    to_char(a.START,'DD-MON-YYYY'),
    to_char(b.END,'DD-MON-YYYY'),
    a.stu
    b.value


    This returns me the following:

    18-ABR-2016 18-ABR-2016 tt 1 9999
    18-ABR-2016 18-ABR-2016 tt 1 4444
    18-ABR-2016 18-ABR-2016 tt 1 11121


    But so it is the same as before, I repeated information, it should be:

    18-ABR-2016 18-ABR-2016 tt 1 9999 4444 11121


    SELECT
    to_char(a.START,'DD-MON-YYYY'),
    to_char(a.END,'DD-MON-YYYY'),
    cast(a.stu as char(10)),
    COUNT(*),
    (select LISTAGG(RTRIM(b.value),',') WITHIN GROUP (ORDER BY b.ID_A) from tableB b where a.id = b.ID_A) as VALUE FROM tableA a
    GROUP BY
    to_char(a.START,'DD-MON-YYYY'),
    to_char(b.END,'DD-MON-YYYY'),
    a.stu
    b.value


    but I do not understand what to put in the section of GROUP BY.

    If I put b.value in group by section like the previous query

    I get the following error message:

    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:
    Error en la línea: 16, columna: 7


    what I should put in group by to work as I want ?

    From already thank you very much

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    You can't use START as a column name; it is a reserved keyword. Therefore, I renamed it to C_START. Personally, I wouldn't use END either (it is allowed, though, but is confusing as it very much looks like a PL/SQL block terminator - BEGIN ... END).

    What happens if you just include LISTAGG into your first query, such as
    Code:
      SELECT TO_CHAR (a.c_start, 'DD-MON-YYYY'),
             TO_CHAR (a.c_end, 'DD-MON-YYYY'),
             CAST (a.stu AS CHAR (10)),
             COUNT (*),
             b.VALUE,
             LISTAGG (RTRIM (b.VALUE), ',') WITHIN GROUP (ORDER BY b.ID_A) a_list --> this
        FROM tableA a INNER JOIN tableB b ON a.id = b.ID_A
    GROUP BY TO_CHAR (a.c_start, 'DD-MON-YYYY'),
             TO_CHAR (b.c_end, 'DD-MON-YYYY'),
             a.stu,
             b.VALUE

  5. #5
    Join Date
    Apr 2016
    Posts
    7
    Hi, I run the query and did not work as we wanted. I obtained the following results:


    18-ABR-2016 18-ABR-2016 tt 1 9999 9999
    18-ABR-2016 18-ABR-2016 tt 1 4444 4444
    18-ABR-2016 18-ABR-2016 tt 1 11121 11121


    It created a new a_list column. But he not grouped nothing .

    thank you in advance

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Could you, please, post a test case (CREATE TABLE along with INSERT INTO sample data) so that we could try it?

  7. #7
    Join Date
    Apr 2016
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Could you, please, post a test case (CREATE TABLE along with INSERT INTO sample data) so that we could try it?
    Hello, here you are the create and insert scripts and the querys:


    Code:
    CREATE TABLE TABLEA 
     ( 
         ID NUMBER(*,0) NOT NULL ENABLE, 
         C_START TIMESTAMP (6), 
          C_END TIMESTAMP (6), 
          STU VARCHAR2(256 CHAR)
        );
        
        ALTER TABLE TABLEA  
        ADD CONSTRAINT TABLEA_PK PRIMARY KEY ( ID ) ;
        
     
      CREATE TABLE TABLEB
     ( 
         ID NUMBER(*,0) NOT NULL ENABLE, 
         ID_A NUMBER(*,0) NOT NULL ENABLE, 
         VALUE VARCHAR2(256 CHAR)
        );
        
        ALTER TABLE TABLEB  
        ADD CONSTRAINT TABLEB_PK PRIMARY KEY ( ID ) ;
        
        ALTER TABLE TABLEB  
        ADD CONSTRAINT TABLEA_FK FOREIGN KEY 
        ( 
         ID_A
        ) 
        REFERENCES TABLEA
        ( 
         ID
        ) ;
        
        INSERT INTO TABLEA VALUES (1,to_timestamp('18/02/2014 06:37:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),to_timestamp('18/02/2014 06:38:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),'22');
        
        INSERT INTO TABLEB VALUES (1,1,'TTTT');
        INSERT INTO TABLEB VALUES (2,1,'AAAA');
        INSERT INTO TABLEB VALUES (3,1,'IIII');
        
        INSERT INTO TABLEA VALUES (2,to_timestamp('18/02/2014 08:37:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),to_timestamp('18/02/2014 08:38:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),'33');
           
        INSERT INTO TABLEB VALUES (4,2,'TTTT');
        INSERT INTO TABLEB VALUES (5,2,'BBBB');
        INSERT INTO TABLEB VALUES (6,2,'IIII');   
                 
        INSERT INTO TABLEA VALUES (3,to_timestamp('18/02/2014 09:37:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),to_timestamp('18/02/2014 09:38:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),'22');
                
        INSERT INTO TABLEB VALUES (7,3,'TTTT');
        INSERT INTO TABLEB VALUES (8,3,'AAAA');
        INSERT INTO TABLEB VALUES (9,3,'IIII');           
                
        INSERT INTO TABLEA VALUES (4,to_timestamp('18/02/2014 10:37:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),to_timestamp('18/02/2014 10:38:43,430000000 PM','DD/MM/RRRR HH12:MI:SSXFF AM'),'33');
        
        INSERT INTO TABLEB VALUES (10,4,'TTTT');
        INSERT INTO TABLEB VALUES (11,4,'AAAA');
        INSERT INTO TABLEB VALUES (12,4,'IIII');


    If I run this query:

    Code:
    select 
             ID,
             TO_CHAR (a.c_start, 'DD-MON-YYYY') AS C_START,
             TO_CHAR (a.c_end, 'DD-MON-YYYY') AS C_END,
             CAST (a.stu AS CHAR (10)) AS STU,
            (select LISTAGG(RTRIM(b.value),',') WITHIN GROUP (ORDER BY b.ID_A) from TABLEB b  where a.id = b.id_a) as VALUE 
            from TABLEA a ;
    Im get the following result:

    1 18-FEB-2014 18-FEB-2014 22 AAAA,IIII,TTTT
    2 18-FEB-2014 18-FEB-2014 33 BBBB,IIII,TTTT
    3 18-FEB-2014 18-FEB-2014 22 AAAA,IIII,TTTT
    4 18-FEB-2014 18-FEB-2014 33 AAAA,IIII,TTTT

    THAT IS OK!


    If I run this another query:

    Code:
    SELECT 
             TO_CHAR (a.c_start, 'DD-MON-YYYY') AS C_START,
             TO_CHAR (a.c_end, 'DD-MON-YYYY') AS C_END,
             CAST (a.stu AS CHAR (10)) AS STU,
             COUNT (*),
             b.VALUE,
             LISTAGG (RTRIM (b.VALUE), ',') WITHIN GROUP (ORDER BY b.ID_A) a_list --> this
            FROM TABLEA a INNER JOIN TABLEB b ON a.id = b.ID_A
            GROUP BY 
             TO_CHAR (a.c_start, 'DD-MON-YYYY'),
             TO_CHAR (a.c_end, 'DD-MON-YYYY'),
             a.stu,
             b.VALUE;

    Im getting this result:

    18-FEB-2014 18-FEB-2014 22 2 AAAA AAAA,AAAA
    18-FEB-2014 18-FEB-2014 22 2 IIII IIII,IIII
    18-FEB-2014 18-FEB-2014 22 2 TTTT TTTT,TTTT
    18-FEB-2014 18-FEB-2014 33 1 AAAA AAAA
    18-FEB-2014 18-FEB-2014 33 1 BBBB BBBB
    18-FEB-2014 18-FEB-2014 33 2 IIII IIII,IIII
    18-FEB-2014 18-FEB-2014 33 2 TTTT TTTT,TTTT


    But really should get :

    18-FEB-2014 18-FEB-2014 22 2 AAAA,IIII,TTTT
    18-FEB-2014 18-FEB-2014 33 1 BBBB,IIII,TTTT
    18-FEB-2014 18-FEB-2014 33 1 AAAA,IIII,TTTT



    thank you in advance

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Thank you for the test case!

    Here you go:
    Code:
    SQL> WITH test1
      2       AS (SELECT a.id,
      3                  a.stu,
      4                  b.VALUE,
      5                  TO_CHAR (a.c_start, 'DD-MON-YYYY') c_start,
      6                  TO_CHAR (a.c_end, 'DD-MON-YYYY') c_end
      7             FROM tablea a, tableb b
      8            WHERE a.id = b.id_a),
      9       test2
     10       AS (  SELECT stu,
     11                    LISTAGG (VALUE, ',') WITHIN GROUP (ORDER BY id, stu) lista,
     12                    c_start,
     13                    c_end
     14               FROM test1
     15           GROUP BY stu, id, c_start, c_end)
     16    SELECT c_start,
     17           c_end,
     18           COUNT (*) cnt,
     19           stu,
     20           lista
     21      FROM test2
     22  GROUP BY c_start, c_end, stu, lista
     23  ORDER BY c_start, c_end, stu, lista;
    
    C_START              C_END                       CNT STU LISTA
    -------------------- -------------------- ---------- --- --------------------
    18-FEB-2014          18-FEB-2014                   2 22  AAAA,IIII,TTTT
    18-FEB-2014          18-FEB-2014                   1 33  AAAA,IIII,TTTT
    18-FEB-2014          18-FEB-2014                   1 33  BBBB,IIII,TTTT
    
    SQL>

  9. #9
    Join Date
    Apr 2016
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Thank you for the test case!

    Here you go:
    Code:
    SQL> WITH test1
      2       AS (SELECT a.id,
      3                  a.stu,
      4                  b.VALUE,
      5                  TO_CHAR (a.c_start, 'DD-MON-YYYY') c_start,
      6                  TO_CHAR (a.c_end, 'DD-MON-YYYY') c_end
      7             FROM tablea a, tableb b
      8            WHERE a.id = b.id_a),
      9       test2
     10       AS (  SELECT stu,
     11                    LISTAGG (VALUE, ',') WITHIN GROUP (ORDER BY id, stu) lista,
     12                    c_start,
     13                    c_end
     14               FROM test1
     15           GROUP BY stu, id, c_start, c_end)
     16    SELECT c_start,
     17           c_end,
     18           COUNT (*) cnt,
     19           stu,
     20           lista
     21      FROM test2
     22  GROUP BY c_start, c_end, stu, lista
     23  ORDER BY c_start, c_end, stu, lista;
    
    C_START              C_END                       CNT STU LISTA
    -------------------- -------------------- ---------- --- --------------------
    18-FEB-2014          18-FEB-2014                   2 22  AAAA,IIII,TTTT
    18-FEB-2014          18-FEB-2014                   1 33  AAAA,IIII,TTTT
    18-FEB-2014          18-FEB-2014                   1 33  BBBB,IIII,TTTT
    
    SQL>


    Excellent, thank you very much for your help!

  10. #10
    Join Date
    Apr 2016
    Posts
    7
    Hi, I tried to add more data : average , max , min and mode .
    But all the results I returns to 0.

    What am I doing wrong?

    thank you in advance .



    WITH test1
    AS (SELECT a.id,
    a.stu,
    b.VALUE,
    TO_CHAR (a.c_start, 'DD-MON-YYYY') c_start,
    TO_CHAR (a.c_end, 'DD-MON-YYYY') c_end
    FROM tablea a, tableb b
    WHERE a.id = b.id_a),
    test2
    AS ( SELECT stu,
    LISTAGG (VALUE, ',') WITHIN GROUP (ORDER BY id, stu) lista,
    c_start,
    c_end
    FROM test1
    GROUP BY stu, id, c_start, c_end)
    SELECT c_start,
    c_end,
    COUNT (*) cnt,
    stu,
    lista,
    TO_CHAR(avg(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9990') as AVG,
    TO_CHAR(max(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990') as MAX,
    TO_CHAR(MIN(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990') as MIN,
    TO_CHAR(STATS_MODE(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990') AS MODE
    FROM test2
    GROUP BY c_start, c_end, stu, lista
    ORDER BY c_start, c_end, stu, lista;

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Certainly it does return 0, because you decided to remove all but date information from C_START and C_END columns; remember, you selected
    Code:
    TO_CHAR (a.c_start, 'DD-MON-YYYY') c_start,
    TO_CHAR (a.c_end, 'DD-MON-YYYY') c_end
    so no time information (hours, minutes, ...) has left.

    Both of them result in 18.02.2014 so when you subtract those two (exactly the same) values, you get 0 (zero).

  12. #12
    Join Date
    Apr 2016
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    Certainly it does return 0, because you decided to remove all but date information from C_START and C_END columns; remember, you selected
    Code:
    TO_CHAR (a.c_start, 'DD-MON-YYYY') c_start,
    TO_CHAR (a.c_end, 'DD-MON-YYYY') c_end
    so no time information (hours, minutes, ...) has left.

    Both of them result in 18.02.2014 so when you subtract those two (exactly the same) values, you get 0 (zero).


    Ok , I get it .

    So , I just change the query test1 for dates come complete with hours, minutes and seconds. And then in the last query I get the average, maximum, minimum and mode and group only with DD-MM-YYYY.

    The query was me:

    Code:
    WITH test1
             AS (SELECT a.id,
                        a.stu,
                        b.VALUE,
                        a.c_start,
                        a.c_end
                   FROM tablea a, tableb b
                  WHERE a.id = b.id_a),
             test2
            AS (  SELECT stu,
                         LISTAGG (VALUE, ',') WITHIN GROUP (ORDER BY id, stu) lista,
                         c_start,
                         c_end
                    FROM test1
                GROUP BY stu, id, c_start, c_end)
         SELECT TO_CHAR (c_start, 'DD-MON-YYYY'),
                TO_CHAR (c_end, 'DD-MON-YYYY'),
                COUNT (*) cnt,
                stu,
                lista,
                TO_CHAR(avg(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9990')  as AVG,
                TO_CHAR(max(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990') as MAX,
                TO_CHAR(MIN(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990') as MIN, 
                TO_CHAR(STATS_MODE(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9999999990')  AS C_MODE 
           FROM test2
       GROUP BY TO_CHAR (c_start, 'DD-MON-YYYY'), TO_CHAR (c_end, 'DD-MON-YYYY'), stu, lista
       ORDER BY TO_CHAR (c_start, 'DD-MON-YYYY'), TO_CHAR (c_end, 'DD-MON-YYYY'), stu, lista;
    It causes me a bit of intrigue that all results give me 60, but I guess it will be for the hours they have inserted .

    18-FEB-2014 18-FEB-2014 2 22 AAAA,IIII,TTTT 60.0000 60.0000000000 60.0000000000 60.0000000000
    18-FEB-2014 18-FEB-2014 1 33 AAAA,IIII,TTTT 60.0000 60.0000000000 60.0000000000 60.0000000000
    18-FEB-2014 18-FEB-2014 1 33 BBBB,IIII,TTTT 60.0000 60.0000000000 60.0000000000 60.0000000000

    What do you think? I'm getting the results of avg, max, min and mode right?

    thank you in advance

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,088
    Provided Answers: 4
    Difference between all C_START and C_END values (in your sample data) is exactly 1 minute. For example:
    Code:
    18/02/2014 06:37:43,430000000
    18/02/2014 06:38:43,430000000
    When subtracting dates in Oracle, the result is number of days. In your case (disregard rounding):
    Code:
    SQL> select to_date('18.02.2016 06:38:43', 'dd.mm.yyyy hh24:mi:ss')
      2       - to_date('18.02.2016 06:37:43', 'dd.mm.yyyy hh24:mi:ss') diff
      3  from dual;
    
          DIFF
    ----------
    ,000694444
    
    SQL> select 0.000694444 * 24 hours,
      2         0.000694444 * 24 * 60 minutes,
      3         0.000694444 * 24 * 60 * 60 seconds  --> this is what you have in your query
      4  from dual;
    
         HOURS    MINUTES    SECONDS
    ---------- ---------- ----------
    ,016666656  ,99999936 59,9999616
    
    SQL>
    The "SECONDS" value is the one you are selecting in your query:
    Code:
    TO_CHAR(avg(cast(c_end as date)-cast(c_start as date))*24*60*60,'FM9999999.9990')  as AVG,
    so - that's why you get 60 everywhere.

Posting Permissions

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