How to create a single query with joins using these two below mentioned tables [message #299852] |
Wed, 13 February 2008 06:45  |
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 #300001 is a reply to message #299990] |
Thu, 14 February 2008 00:09   |
 |
Littlefoot
Messages: 21823 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   |
flyboy
Messages: 1903 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   |
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 #300587 is a reply to message #299852] |
Sat, 16 February 2008 05:25   |
flyboy
Messages: 1903 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   |
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
|
|
|
|
|
|