Home » SQL & PL/SQL » SQL & PL/SQL » Table joining (Windows)
| Table joining [message #653729] |
Sun, 17 July 2016 11:50  |
 |
17111991
Messages: 3
Registered: July 2016
Location: karnataka
|
Junior Member |
|
|
Hi everyone,
I need your help.
I have created two views .one is parent view and another child view.after joining these two views i want data to come rowise to its account number.
|
|
|
|
| Re: Table joining [message #653733 is a reply to message #653729] |
Sun, 17 July 2016 12:39  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What does "rowise" mean?
In addition to the recommendations in your previous topic:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
[Updated on: Sun, 17 July 2016 12:39] Report message to a moderator |
|
|
|
| Re: Table joining [message #653734 is a reply to message #653729] |
Sun, 17 July 2016 20:29 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have put Windows where your Oracle version belongs, so I don't know if you are even using Oracle or are in the right forum.
You have titled your post table joining, but you ask about joining views, so I am not sure which you mean or if you know the difference.
The following is a general example for Oracle tables.
Suppose you have parent and child tables with data something like the following.
SCOTT@orcl_12.1.0.2.0> select * from parent_tab order by account_num
2 /
ACCOUNT_NUM ACCOUNT_NAME
----------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
4 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from child_tab order by account_num, transaction_num
2 /
ACCOUNT_NUM TRANSACTION_NUM AMOUNT
----------- --------------- ----------
10 7782 2450
10 7839 5000
10 7934 1300
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
30 7900 950
14 rows selected.
You should have constraints on those tables, consisting of a primary key on the parent table and a foreign key on the child table that references the parent table, as shown below. The primary key constraint prevents duplicate entries in the parent table and the foreign key constraint prevents entries in the child table for which there is no parent in the parent table.
SCOTT@orcl_12.1.0.2.0> alter table parent_tab add constraint pt_an_pk primary key (account_num)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> alter table child_tab add constraint ct_an_fk foreign key (account_num) references parent_tab (account_num)
2 /
Table altered.
You can then query such tables with or without constraints as shown below or you can query views of such tables in the same manner. Your queries will run faster with constraints and/or indexes on the tables. In the select clause, you put whatever columns you want from the tables. In the from clause, you put whatever tables the columns need to be selected from, joining the tables on the columns that they have in common, which are typically those used in the primary and foreign key constraints. In the order by clause, you put whatever columns you want it ordered by.
SCOTT@orcl_12.1.0.2.0> select p.account_num, p.account_name,
2 c.transaction_num, c.amount
3 from parent_tab p join child_tab c on p.account_num = c.account_num
4 order by account_num, transaction_num
5 /
ACCOUNT_NUM ACCOUNT_NAME TRANSACTION_NUM AMOUNT
----------- -------------- --------------- ----------
10 ACCOUNTING 7782 2450
10 ACCOUNTING 7839 5000
10 ACCOUNTING 7934 1300
20 RESEARCH 7369 800
20 RESEARCH 7566 2975
20 RESEARCH 7788 3000
20 RESEARCH 7876 1100
20 RESEARCH 7902 3000
30 SALES 7499 1600
30 SALES 7521 1250
30 SALES 7654 1250
30 SALES 7698 2850
30 SALES 7844 1500
30 SALES 7900 950
14 rows selected.
|
|
|
|
| Re: Table joining [message #653735 is a reply to message #653734] |
Sun, 17 July 2016 21:55 |
|
17111991
Messages: 3
Registered: July 2016
Location: karnataka
|
Junior Member |
|
|
hi all,
I have 3 tables,all are VARCHAR2(2000) datatypes ,no Constraints as such.
Table1:
T_Propduct_Parent
Account_no Prod_id Target_product Dependant_Prod_id
---------- ------- -------------- ----------------
1567 1-156 Broadband,TV channel1 package null
9324 1-678 Broadband,TV channel2 package null
7245 D124 Broadband,TV channel1 package null
Table2:
T_Offer_Details
Product_name Offer1 Offer2 Offer3
----------- ------ ------- -------
Broadband,TV channel1 package service123 Recharge 347 Extra Balance1
Broadband,TV channel2 package service897 Recharge 125 Extra Balance2
Table 3:
T_Product_Child
Account_no Prod_id Target_product Target_product_split Dependant_Prod_id
--------- ------- -------------- ------------------ ----------------
1567 1765 Broadband SMS AND TV large Broadband SMS 1-156;0987
1567 1765 Broadband SMS AND TV large TV large 1-156;0987
9324 9870 Broadband SMS;Voice AND TV large Broadband SMS 4980;1-678;1402
9324 9870 Broadband SMS;Voice AND TV large Voice 4980;1-678;1402
9324 9870 Broadband SMS;Voice AND TV large TV large 4980;1-678;1402
I want my final table to look like below table.
Resultant_table
Account_no Prod_id Target_product Dependant_Prod_id
---------- ------- ------------- ----------------
1567 1-156 Broadband,TV channel1 package null
1567 1765 Broadband SMS 1-156;0987
1567 1765 TV large 1-156;0987
9324 1-678 Broadband,TV channel2 package null
9324 9870 Broadband SMS 4980;1-678;1402
9324 9870 Voice 4980;1-678;1402
9324 9870 TV large 4980;1-678;1402
Hope above explaination is sufficient.
|
|
|
|
| Re: Table joining [message #653736 is a reply to message #653735] |
Sun, 17 July 2016 23:44 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your columns are not aligned, so it is difficult to tell what data is in which column. It also would have helped if you had provided create table and insert statements for the sample data. I don't see why account_no 7245 is not in the result set. I don't know if you accidentally left that row out or if there is some reason for it. Although you have posted the desired results, you have not explained why those results should be.
It appears that the relevant tables, columns, and data are:
SCOTT@orcl_12.1.0.2.0> select account_no, prod_id, target_product, dependant_prod_id
2 from t_product_parent
3 /
ACCOUNT_NO PROD_ID TARGET_PRODUCT DEPENDANT_PROD_ID
---------- ------- ----------------------------- -----------------
1567 1-156 Broadband,TV channel1 package
9324 1-678 Broadband,TV channel2 package
7245 D124 Broadband,TV channel1 package
3 rows selected.
SCOTT@orcl_12.1.0.2.0> select account_no, prod_id, target_product_split, dependant_prod_id
2 from t_product_child
3 /
ACCOUNT_NO PROD_ID TARGET_PRODUCT_SPLIT DEPENDANT_PROD_ID
---------- ------- ----------------------------- -----------------
1567 1765 Broadband SMS 1-156;0987
1567 1765 TV large 1-156;0987
9324 9870 Broadband SMS 4980;1-678;1402
9324 9870 Voice 4980;1-678;1402
9324 9870 TV large 4980;1-678;1402
5 rows selected.
It looks like you just want to UNION ALL the two above together, as shown below.
SCOTT@orcl_12.1.0.2.0> select account_no, prod_id, target_product, dependant_prod_id
2 from t_product_parent
3 union all
4 select account_no, prod_id, target_product_split, dependant_prod_id
5 from t_product_child
6 order by account_no, prod_id, target_product
7 /
ACCOUNT_NO PROD_ID TARGET_PRODUCT DEPENDANT_PROD_ID
---------- ------- ----------------------------- -----------------
1567 1-156 Broadband,TV channel1 package
1567 1765 Broadband SMS 1-156;0987
1567 1765 TV large 1-156;0987
7245 D124 Broadband,TV channel1 package
9324 1-678 Broadband,TV channel2 package
9324 9870 Broadband SMS 4980;1-678;1402
9324 9870 TV large 4980;1-678;1402
9324 9870 Voice 4980;1-678;1402
8 rows selected.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 09:32:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
