Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2013
    Posts
    5

    Unanswered: how to read from Table 1 retrieve data from Table 2

    I'm new using oracle, but I wrote following sentences.
    to know which one are the main courses and which one are the child curses.

    TABLE 1
    If I run this
    select PK1 ,CRSMAIN_PARENT_PK1,CRSMAIN_PK1,DTCREATED from course_course where crsmain_parent_pk1= '7631' order by CRSMAIN_PARENT_PK1

    pk1 Main Child DTCreated
    ----------------------------------------------------
    6517 7631 9897 8/2/2013 11:20:06 AM
    6526 7631 9898 8/2/2013 11:20:06 AM
    6521 7631 9899 8/2/2013 11:20:06 AM
    6523 7631 9900 8/2/2013 11:20:06 AM
    6522 7631 9901 8/2/2013 11:20:06 AM
    6527 7631 9902 8/2/2013 11:20:06 AM
    And more data…

    TABLE 2
    if I run this script I can get the description
    select PK1,COURSE_ID,COURSE_NAME,DTCREATED from course_main t1 where COURSE_ID like 'CA01%' order by COURSE_ID

    7631 CA01-1302 Contabilidad 6/17/2013 5:35:12 PM
    9897 CA01-1302-AD32 Contabilidad 8/1/2013 7:43:49 PM
    9898 CA01-1302-AF21 Contabilidad 8/1/2013 7:43:49 PM
    9899 CA01-1302-AF22 Contabilidad 8/1/2013 7:43:49 PM
    9900 CA01-1302-AX22 Contabilidad 8/1/2013 7:43:50 PM
    And more data…



    I'd like to get something like this.

    Conclusion: It should be something like this. but I dont know how to combine those 2 scripts. ( should I use variables? )

    CRSMAIN_PARENT_PK1 |COURSE_ID CRSMAIN_PK1 |COURSE_ID |COURSE_NAME
    7631 |CA01-1302 |9897 |CA01-1302-AD32 |Contabilidad
    7631 |CA01-1302 |9898|CA01-1302-AF21 |Contabilidad
    7631 |CA01-1302 |9899 |CA01-1302-AF22 |Contabilidad
    7631 |CA01-1302 |9900 |CA01-1302-AX22 |Contabilidad

    7631 description is the same for all of them
    9897,9898,9899,9900 description is different.

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT
    FROM course_course CC, course_main CM
    WHERE CM.PK1 = CC.CHILD
    AND
    ORDER BY
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    5
    Thanks for your reply!

    I still missing the description. I need to read table 1 and get description from table 2.

    I came up with this but I still need the description for crsmain_parent_pk1

    select t1.PK1, t1.crsmain_parent_pk1, t1.crsmain_pk1, t2.COURSE_ID, t2.course_name, t2.DTCREATED
    from COURSE_COURSE t1, COURSE_MAIN t2
    where t2.PK1=t1.CRSMAIN_PK1

    look

    7631 ( i need description here from table 2) 9897 CA01-1302-AD32
    7631 ( i need description here from table 2) 9898 CA01-1302-AF21
    7631 ( i need description here from table 2) 9899 CA01-1302-AF22
    7631 ( i need description here from table 2) 9900 CA01-1302-AX22
    7631 ( i need description here from table 2) 9901 CA01-1302-AX24
    7631 ( i need description here from table 2) 13112 CA01-1302-AX25

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I need to read table 1 and get description from table 2.
    Which column in table2 contains the DESCRIPTION?
    just include the desired column in the SELECT clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2013
    Posts
    5
    Thanks again!
    COURSE_ID contain the description.
    the problem is: when the script read table 2 it will feed parent course or the child depending of the setting I set on WHERE clause.

    I believe that I need to read table 1 and put the info in a variables and then read table 2


    TABLE 2
    select PK1,COURSE_ID from course_main t1 where COURSE_ID like 'CA01%' order by COURSE_ID

    7631 - CA01-1302
    9897 - CA01-1302-AD32
    9898 - CA01-1302-AF21
    9899 - CA01-1302-AF22
    9900 - CA01-1302-AX22

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you provided CREATE TABLE statements for the 2 tables,
    INSERT statement which populate the tables with you sample data
    & the expected & desired results from the sample data, you'll have the SQL soon thereafter
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2013
    Posts
    5
    Thank you again! here you go. I hope this help.

    I need to get report showing
    Note: COURSE_ID needs to be retrieved from T_Titles for COURSE_MAIN_PARENT and for COURSE_MAIN

    ----------------------------------------------------------------
    COURSE_MAIN_PARENT,COURSE_ID, COURSE_MAIN, COURSE_ID , COURSE_NAME, DT-CRE

    7631, TZ01-1813, 9897 , TZ01-1813-AD32 , Contabilidad
    7631, TZ01-1813, 9912 , TZ01-1813-AF21 , Contabilidad
    7632, CA04-1302, 9923 , TZ04-1813-AX51 , Contabilidad avanzada
    7632, CA04-1302, 9924 , TZ04-1813-CA51 , Contabilidad avanzada

    --------------------------------------------------------
    -- Table structure for table `T_Course`
    --------------------------------------------------------

    CREATE TABLE IF NOT EXISTS `T_Course` (
    `pk1` int(11) NOT NULL,
    `COURSE_MAIN_PARENT` int(11) NOT NULL,
    `COURSE_MAIN` int(11) NOT NULL,
    `DT-CRE` datetime NOT NULL,
    `DT-CRE` datetime NOT NULL,
    PRIMARY KEY (`pk1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    ---------------------------------------------------------
    -- Dumping data for table `T_Course`
    ---------------------------------------------------------

    INSERT INTO `T_Course` (`pk1`, `COURSE_MAIN_PARENT`, `COURSE_MAIN`, `DT-CRE`) VALUES
    (6517, 7631, 9897, '0000-00-00 00:00:00'),
    (6521, 7631, 9899, '0000-00-00 00:00:00'),
    (6526, 7631, 9898, '0000-00-00 00:00:00'),
    (6532, 7632, 9924, '0000-00-00 00:00:00'),
    (6533, 7632, 9925, '0000-00-00 00:00:00'),
    (6534, 7632, 9923, '0000-00-00 00:00:00'),
    (6535, 7633, 9927, '0000-00-00 00:00:00'),
    (6536, 7633, 9926, '0000-00-00 00:00:00'),
    (6543, 7639, 9934, '0000-00-00 00:00:00'),
    (6544, 7639, 9936, '0000-00-00 00:00:00'),
    (6545, 7639, 9935, '0000-00-00 00:00:00');

    -- --------------------------------------------------------
    -- Table structure for table `T_Titles`
    -----------------------------------------------------------

    CREATE TABLE IF NOT EXISTS `T_Titles` (
    `PK1` int(11) NOT NULL,
    `COURSE_ID` varchar(100) NOT NULL,
    `COURSE_NAME` varchar(100) NOT NULL,
    `DT_CRE` datetime NOT NULL,
    PRIMARY KEY (`PK1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `T_Titles`
    --

    INSERT INTO `T_Titles` (`PK1`, `COURSE_ID`, `COURSE_NAME`, `DT_CRE`) VALUES
    (7231, 'TZ21-1813', 'Costos y Presupuestos', '0000-00-00 00:00:00'),
    (7232, 'TZ37-1813', 'Contabilidad y Finanzas para la Gestion', '0000-00-00 00:00:00'),
    (7631, 'TZ01-1813', 'Contabilidad', '0000-00-00 00:00:00'),
    (7632, 'TZ04-1813', 'Contabilidad avanzada', '0000-00-00 00:00:00'),
    (7633, 'TZ05-1813', 'Sistemas avanzados de costeo', '0000-00-00 00:00:00'),
    (7639, 'TZ14-1813', 'Analisis y Proyeccion de Estados Financieros', '0000-00-00 00:00:00'),
    (7640, 'TZ59-1813', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (7641, 'TZ62-1813', 'Costos y presupuestos', '0000-00-00 00:00:00'),
    (7642, 'TZ63-1813', 'Consolidacion y Fusion', '0000-00-00 00:00:00'),
    (7644, 'TZ65-1813', 'Taller Tributario', '0000-00-00 00:00:00'),
    (7645, 'TZ67-1813', 'Instrumentos para la Gestion', '0000-00-00 00:00:00'),
    (7646, 'TZ69-1813', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (7647, 'TZ71-1813', 'Integracion Contable', '0000-00-00 00:00:00'),
    (7648, 'TZ72-1813', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (7649, 'TZ73-1813', 'NIIFs para empresas globales', '0000-00-00 00:00:00'),
    (7650, 'TZ74-1813', 'Taller de NIIF S', '0000-00-00 00:00:00'),
    (9897, 'TZ01-1813-AD32', 'Contabilidad', '0000-00-00 00:00:00'),
    (9898, 'TZ01-1813-AF21', 'Contabilidad', '0000-00-00 00:00:00'),
    (9899, 'TZ01-1813-AF22', 'Contabilidad', '0000-00-00 00:00:00'),
    (9923, 'TZ04-1813-AX51', 'Contabilidad avanzada', '0000-00-00 00:00:00'),
    (9924, 'TZ04-1813-CA51', 'Contabilidad avanzada', '0000-00-00 00:00:00'),
    (9925, 'TZ04-1813-CA52', 'Contabilidad avanzada', '0000-00-00 00:00:00'),
    (9926, 'TZ05-1813-CA51', 'Sistemas avanzados de costeo', '0000-00-00 00:00:00'),
    (9927, 'TZ05-1813-CA52', 'Sistemas avanzados de costeo', '0000-00-00 00:00:00'),
    (9934, 'TZ14-1813-CA33', 'Analisis y Proyeccion de Estados Financieros', '0000-00-00 00:00:00'),
    (9935, 'TZ14-1813-CA61', 'Analisis y Proyeccion de Estados Financieros', '0000-00-00 00:00:00'),
    (9936, 'TZ14-1813-CA62', 'Analisis y Proyeccion de Estados Financieros', '0000-00-00 00:00:00'),
    (9937, 'TZ59-1813-S131', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (9938, 'TZ59-1813-S132', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (9939, 'TZ59-1813-SW31', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (9940, 'TZ59-1813-SX31', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (9941, 'TZ59-1813-WX31', 'Contabilidad y Presupuestos', '0000-00-00 00:00:00'),
    (9960, 'TZ65-1813-CA82', 'Taller Tributario', '0000-00-00 00:00:00'),
    (9969, 'TZ69-1813-AX11', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (9970, 'TZ69-1813-AX12', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (9971, 'TZ69-1813-CA11', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (9972, 'TZ69-1813-CA12', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (9973, 'TZ69-1813-TV11', 'Fundamentos de Contabilidad', '0000-00-00 00:00:00'),
    (9974, 'TZ71-1813-CA42', 'Integraci?n Contable', '0000-00-00 00:00:00'),
    (9975, 'TZ71-1813-CA43', 'Integraci?n Contable', '0000-00-00 00:00:00'),
    (9976, 'TZ72-1813-AD41', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9977, 'TZ72-1813-AF31', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9978, 'TZ72-1813-AF32', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9979, 'TZ72-1813-AX31', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9980, 'TZ72-1813-AX32', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9981, 'TZ72-1813-AX33', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (9993, 'TZ74-1813-CAA1', 'Taller de NIIF S', '0000-00-00 00:00:00'),
    (13052, 'TZ76-1813', 'Sistemas Avanzados De Costeo (BLENDED)', '0000-00-00 00:00:00'),
    (13117, 'TZ62-1813-AM5A', 'Costos y presupuestos', '0000-00-00 00:00:00'),
    (13118, 'TZ71-1813-AX41', 'Integraci?n Contable', '0000-00-00 00:00:00'),
    (13119, 'TZ72-1813-CA38', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13120, 'TZ72-1813-CA39', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13121, 'TZ72-1813-CA3F', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13122, 'TZ72-1813-CA3G', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13123, 'TZ72-1813-CA3H', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13124, 'TZ72-1813-CA40', 'Contabilidad intermedia', '0000-00-00 00:00:00'),
    (13125, 'TZ73-1813-CAA1', 'NIIFs para empresas globales', '0000-00-00 00:00:00'),
    (13360, 'TZ76-1813-CA51', 'Sistemas Avanzados De Costeo (BLENDED)', '0000-00-00 00:00:00'),
    (13361, 'TZ76-1813-CA52', 'Sistemas Avanzados De Costeo (BLENDED)', '0000-00-00 00:00:00'),
    (13477, 'TZ67-1813-CP5F', 'Instrumentos para la Gesti?n', '0000-00-00 00:00:00'),
    (14109, 'TZ19-1813-Q23B', 'Contabilidad', '0000-00-00 00:00:00'),
    (14110, 'TZ19-1813-Q24A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14111, 'TZ19-1813-Q25B', 'Contabilidad', '0000-00-00 00:00:00'),
    (14112, 'TZ19-1813-Q26A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14113, 'TZ19-1813-Q28A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14114, 'TZ19-1813-Q2CA', 'Contabilidad', '0000-00-00 00:00:00'),
    (14115, 'TZ19-1813-Q2DB', 'Contabilidad', '0000-00-00 00:00:00'),
    (14116, 'TZ19-1813-Q2EB', 'Contabilidad', '0000-00-00 00:00:00'),
    (14117, 'TZ19-1813-Q2GA', 'Contabilidad', '0000-00-00 00:00:00'),
    (14118, 'TZ19-1813-S21A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14119, 'TZ19-1813-S23A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14120, 'TZ19-1813-S24B', 'Contabilidad', '0000-00-00 00:00:00'),
    (14121, 'TZ19-1813-S25A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14122, 'TZ19-1813-V23A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14123, 'TZ19-1813-V25A', 'Contabilidad', '0000-00-00 00:00:00'),
    (14124, 'TZ19-1813-V26B', 'Contabilidad', '0000-00-00 00:00:00'),
    (14125, 'TZ19-1813-V27B', 'Contabilidad', '0000-00-00 00:00:00'),
    (14149, 'TZ37-1813-Q86A', 'Contabilidad y Finanzas para la Gesti?n', '0000-00-00 00:00:00'),
    (14978, 'TZ37-1813-Q81A', 'Contabilidad y Finanzas para la Gesti?n', '0000-00-00 00:00:00'),
    (15105, 'TZ21-1813-N32A', 'Costos y Presupuestos', '0000-00-00 00:00:00'),
    (15324, 'TZ19-1813-S27A', 'Contabilidad', '0000-00-00 00:00:00'),
    (15325, 'TZ21-1813-U31A', 'Costos y Presupuestos', '0000-00-00 00:00:00'),
    (15347, 'TZ19-1813-S28B', 'Contabilidad', '0000-00-00 00:00:00'),
    (15348, 'TZ21-1813-Q33B', 'Costos y Presupuestos', '0000-00-00 00:00:00'),
    (15349, 'TZ37-1813-Q84B', 'Contabilidad y Finanzas para la Gesti?n', '0000-00-00 00:00:00');

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I don't have Oracle or MySQL, but see if this works.

    Code:
    select c.Course_Main_Parent, t1.Course_ID, c.Course_Main, t.Course_ID, t.Course_Name
      from T_Course c
      inner join T_Titles t  on t.pk1  = c.Course_Main
      inner join T_Titles t1 on t1.pk1 = c.Course_Main_Parent

  9. #9
    Join Date
    Sep 2013
    Posts
    5
    look like its working, I'll do a couple of test. and I'll get back to you.
    Thank you Very much!

Posting Permissions

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