Need to generate report from main table and master table

Albert Chao

Master table

create table master_table(mast_id number, jira_status varchar2(50));

insert into master_table values(1,'In DEV');
insert into master_table values(2,'In SIT');
insert into master_table values(3,'In UAT');

Main table

create table tab_main(id_num number, first_name, jira_code);
insert into tab_main(1,'A',--Here I want to fetch the code from master_table)

Finally, I want a report where in I would select required columns

select tm.id_num, tm.first_name, mt.jira_status from main able and master table

Frank Kulash

Hi, @Albert Chao

The CREATE TABLE statement you posted for tab_main

create table tab_main(id_num number, first_name, jira_code);

won't work; two of the columns are missing data types.

Assuming you have created the table, you can populate it like this:

INSERT INTO tab_main (id_num, first_name, jira_code)
              VALUES (1,      'A',        1);

and the following query produces the desired output:

SELECT t m.id_num, tm.first_name
,	mt.mast_id, mt.jira_status
FROM	tab_main   tm
JOIN	master_table mt ON mt.mast_id = tm.jira_code
;

I'm assuming that master_table.mast_id and tab_main.id_num are primary keys, which have no meaning besides uniquely identifying rows in their tables, and it's just coincidence that some mast_id has the same value as some id_num.

BEDE

select mt.id_num, mt.first_name, mt.jira_status, mst.*

from tab_main mt

join master_table mst on mt.id_num=mst.mast_id

;

I suppose the select should be so.

Still, to make things clear, you should give the primary keys for those tables, and, eventually, the foreign key(s).

Is it possible to have a row in master_table with no corresponding row in tab_main? Is it possible to have a row in tab_main with no corresponding rows in master_table? Not having the foreign key I can't answer the questions above. Depending on that, there may be good to use an outer join.

Albert Chao

@BEDE Yes. tab_main has id_num as PK which is FK in master table. and master_table has mast_id as PK. I want to insert jira_code into tab_main and based on the jira_code I need to display jira_status of master_tab

Frank Kulash

Hi,, @Albert Chao

It's not at all clear what you want.

I want to insert jira_code into tab_main and based on the jira_code

Show exactly what tab_main should look like after the INSERT is finished, given the sample data you posted in your first message.

I need to display jira_status of master_tab

Show the exact output you want.

Albert Chao

@Frank Kulash

create table tab_main(id_num number, first_name, jira_code);
insert into tab_main(1,'A',--Here I want to fetch the code from master_table)

After insertion this table should be like :

Then, based on tab_main table and master_table I want to to select id_num, first_name from tab_main table and jira_status from master_table.

Expected output:

Frank Kulash

Hi, @Albert Chao

The CREATE TABLE statement you posted for tab_main

create table tab_main(id_num number, first_name, jira_code);

won't work; two of the columns are missing data types.

Assuming you have created the table, you can populate it like this:

INSERT INTO tab_main (id_num, first_name, jira_code)
              VALUES (1,      'A',        1);

and the following query produces the desired output:

SELECT t m.id_num, tm.first_name
,	mt.mast_id, mt.jira_status
FROM	tab_main   tm
JOIN	master_table mt ON mt.mast_id = tm.jira_code
;

I'm assuming that master_table.mast_id and tab_main.id_num are primary keys, which have no meaning besides uniquely identifying rows in their tables, and it's just coincidence that some mast_id has the same value as some id_num.

Albert Chao

@Frank Kulash Sorry for the correction.

INSERT INTO tab_main (id_num, first_name, jira_code)
              VALUES (1,      'A',        1);

Here we need jira_code from the master_table. So, how I will fetch mast_id from the master_table. then the fetched value i.e mast_id I will insert into tab_main.

Frank Kulash

Hi, @Albert Chao

The code I posted does exactly what you requested in this example. If it doesn't do what you want for some other sample data, then post that sample data and the desired results. Explain (in general terms, with specific examples) how you get the desired results from the given data.

Here we need jira_code from the master_table. So, how I will fetch mast_id from the master_table. then the fetched value i.e mast_id I will insert into tab_main.

Why do you want only one row in tab_main? Why not two, or three? Given that you only wand one row, why do you want it to have jira_code=1? Why not 2, or 3?

In tab_main, will id_code always be the same as jira_code? If so, why do you want two separate columns?

mathguy

In your earlier thread you said you figured this out already.

You awarded yourself the "accepted answer" for your discovery - but now you still have the same question? Brilliant!

Then: You say you want to insert "the code" from master_table. But in master_table you have only jira_status, not jira_code. Are they the same thing? (Normally they shouldn't be; the "code" should be a number, like 1, 2, 3 and the "status" should be a description like "in dev", "in UAT" etc.)

If in fact they are the same thing, perhaps you are trying to do this:

insert into tab_main (id_num, first_name, jira_code)
    values (1, 'A', (select jira_status from master_table where mast_id = 1));

In a properly designed database, you would have a separate table just for jira status and code; both your tables would only use the code, not the status.

In your attempt, you are missing the mandatory keyword VALUES. You have it in the INSERT statements for the first table, why is it missing for the second table?

Also, while you don't need to include the column names if you are inserting values in all the columns, it is still a best practice to include them in all cases (even if it's technically not mandatory).