Home » SQL & PL/SQL » SQL & PL/SQL » How to create a single query with joins using these two below mentioned tables
How to create a single query with joins using these two below mentioned tables [message #299852] Wed, 13 February 2008 06:45 Go to next message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
Hi Everbody,
I want to share one problem with u.
I am having two tables. one is the parent table containing some fields like:-
empid,primary_person_empid,secondary_person_empid


child table contains
primary_person_empid,contact_no1,contact_no2,secondary_person_empid,contact_no1,contact_no2


output required is
empno,primary person,contact_no1(primary_person),contact_no2(primary_person),secondary_person, contact_no1(secondary_person), contact_no2(secondary_person)

I know that these tables are not properly normalized,i cant change there structure.

please find the solution in a single query with having joins.


Re: How to create a single query with joins using these two below mentioned tables [message #299856 is a reply to message #299852] Wed, 13 February 2008 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are a newbie: post in newbie forum and show us what you already ytried.

If you are an expert, as you posted in expert forum, tell us why you can't do it.

In addition, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: How to create a single query with joins using these two below mentioned tables [message #299862 is a reply to message #299852] Wed, 13 February 2008 07:19 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
child table contains
primary_person_empid,contact_no1,contact_no2,secondary_person_empid,contact_no1,contact_no2


Can a table have duplicate column names?
As in the case of provided data contact_no1,contact_no2 column names are repeated... why ?


Re: How to create a single query with joins using these two below mentioned tables [message #299863 is a reply to message #299862] Wed, 13 February 2008 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can a table have duplicate column names?

No.

Regards
Michel
Re: How to create a single query with joins using these two below mentioned tables [message #299866 is a reply to message #299852] Wed, 13 February 2008 07:34 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I know the answer,

as you see his(rbhatia07) post he used the same column names twice.
That's why i asked him that question ...
Re: How to create a single query with joins using these two below mentioned tables [message #299990 is a reply to message #299862] Wed, 13 February 2008 23:29 Go to previous messageGo to next message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
I know that a table doesnt contain duplicate column names.
In my context these are 2 different fields-
contact_no1 contains the primary phone no say mobile no.,
contact_no2 contains the secondary phone_no say landline no.

As i have written earlier that i can not change the structure of that table even it is not normalized.
Re: How to create a single query with joins using these two below mentioned tables [message #300001 is a reply to message #299990] Thu, 14 February 2008 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rbhatia07 wrote on Thu, 14 February 2008 06:29
I know that a table doesnt contain duplicate column names.
In my context these are 2 different fields-
contact_no1 contains the primary phone no say mobile no.,
contact_no2 contains the secondary phone_no say landline no.


What are these, then?
Quote:
child table contains
primary_person_empid,contact_no1,contact_no2,secondary_person_empid,contact_no1,contact_no2

Re: How to create a single query with joins using these two below mentioned tables [message #300010 is a reply to message #299852] Thu, 14 February 2008 00:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I am having two tables. one is the parent table containing some fields like:-
empid,primary_person_empid,secondary_person_empid

child table contains
primary_person_empid,contact_no1,contact_no2,secondary_person_empid,contact_no1,contact_no2

output required is
empno,primary person,contact_no1(primary_person),contact_no2(primary_person),secondary_person, contact_no1(secondary_person), contact_no2(secondary_person)

May I know, how EMPNO shall be obtained as it is not present in any of two mentioned tables. Also curious whether PRIMARY_PERSON_EMPID in both tables is the same as PRIMARY PERSON in output.
Quote:
I know that these tables are not properly normalized,i cant change there structure.

please find the solution in a single query with having joins.

Using joins means you KNOW the relationship of the two tables. You made no remark about it in your post. As the design is not proper, it may be ambiguous (eg. primary and secondary person in one row or split into more rows). Is it possible to have more eg. primary persons to one EMPID? If no, how is it ensured?

Rather than describing this, post a testcase (CREATE TABLE + INSERT sample data; small, but covering all possible combinations) with expected result.
Re: How to create a single query with joins using these two below mentioned tables [message #300446 is a reply to message #300010] Fri, 15 February 2008 07:44 Go to previous messageGo to next message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
Try to explain the same problem with an example.

Parent Table say Emp_details contains data:-

Empid Primary_person_emp_id secondary_person_empid
aa12345 rb2000 qq22000
ss12345 dd54321 gg55555

Here empid say(aa12345,ss12345) have there primary and secondary superiors.
in case primary supervisor is not there then they report to secondary, that is why secondary_person_empid field is there.

Now the second table say emp_info contains :


Primary_person_emp_id pr_contact_no1 pr_contact_no2
rb2000 777777 666666
dd54321 333333 444444

secondary_person_empid sec_contact_no1 sec_contact_no2
qq22000 888888 8899999
gg55555 111111 222222


if there are only three fields (empid,contact_no1,contact_no2)
then there is no such problem to get output .
But the problem is, the above table contains many more data fields and unable to ignore this kind of structure.

The required output is:-

Empid Primary_person_emp_id Pr_contact_no1 Pr_contact_no2
aa12345 rb2000 777777 666666
ss12345 dd54321 333333 444444


secondary_person_empid sec_contact_no1 sec_contact_no2
qq22000 888888 8899999
gg55555 111111 222222


All primary and secondary person info are in a single row corresponding to empid
Re: How to create a single query with joins using these two below mentioned tables [message #300449 is a reply to message #300446] Fri, 15 February 2008 07:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Please use CODE tags in your post. Do you really think people (including yourself) can make out what data belongs to what columns?
Please read the first post in the forum if you do not know what I am talking about.
Re: How to create a single query with joins using these two below mentioned tables [message #300451 is a reply to message #300446] Fri, 15 February 2008 07:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Table emp_info cannot contain two different sets of columns.
Re: How to create a single query with joins using these two below mentioned tables [message #300587 is a reply to message #299852] Sat, 16 February 2008 05:25 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Rather than describing this, post a testcase (CREATE TABLE + INSERT sample data; small, but covering all possible combinations) with expected result.

Well, you decided to post no code, no data model, just a strange example (as mentioned in previous posts), so I will describe the ideas in words too.

Quote:
please find the solution in a single query with having joins.
It really seems like simple inner joining the child table twice (or two child tables) to the parent table (your first post), or two queries with one join (your latest post; it is contradicting the first one as you show TWO result sets now - maybe result of UNION ALL?).
Did you try it? What problem with a simple JOIN do you have?

Some problems may occur, when the relationship parent -> child is not 1:1 (your example contains only this).
However these situations may happen:
- there is no row in child table -> use outer join
- there are more rows in child tables, so you will get all their combinations; you may try reduce the combinations by using similar approach as described here.

Re: How to create a single query with joins using these two below mentioned tables [message #301393 is a reply to message #299852] Wed, 20 February 2008 07:37 Go to previous messageGo to next message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
Sorry All,for the Inconvenience Caused by U.
Please Ignore my earlier posts.

The Problem is (simpler way to describe):-

Parent Table
Empid     Primary_empid   secondary_empid
aa12345   rb2000          qq22000


Chield Table

Primary_empid  pr_contact_no1  pr_contact_no2  secondary_empid  sec_contact_no1  sec_contact_no2 

rb2000         777777          666666          asd5000          435564            4355678
ZZw345         654234          658888          qq22000          888888            8899999



Required Output is

Empid  Primary_empid  pr_contact_no1  pr_contact_no2  secondary_empid  sec_contact_no1  sec_contact_no2 

aa12345  rb2000       777777          666666          qq22000          888888            8899999

Re: How to create a single query with joins using these two below mentioned tables [message #301395 is a reply to message #301393] Wed, 20 February 2008 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd agree with you, Flyboy.
flyboy
It really seems like simple inner joining the child table twice (or two child tables) to the parent table
Re: How to create a single query with joins using these two below mentioned tables [message #301396 is a reply to message #301393] Wed, 20 February 2008 07:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you try? This looks quite easy.
(but don't get me started about the design!)
Re: How to create a single query with joins using these two below mentioned tables [message #302155 is a reply to message #301395] Sun, 24 February 2008 03:53 Go to previous message
rbhatia07
Messages: 7
Registered: February 2008
Location: Kolkata
Junior Member
Thanks all,

for the solution.
actually i tried to get the required output by using one inner join with child table and case statements but i get that result in two rows.
using of 2 inner joins with child table is the perfect solution, that i did not get in my mind that time.

i will try the same.

thanks
Previous Topic: How can I continue insertion by skipping invalid records?
Next Topic: DBMS_OUTPUT package and "set serveroutput on" command
Goto Forum:
  


Current Time: Sun Dec 04 23:06:38 CST 2016

Total time taken to generate the page: 0.19964 seconds