Home » SQL & PL/SQL » SQL & PL/SQL » Difficulty in joining two tables!
Difficulty in joining two tables! [message #293488] 
Sun, 13 January 2008 06:34 
chepi3000
Messages: 5 Registered: January 2008 Location: CHONGQING

Junior Member 


Hi,everyone!I have two tables
T1
s t
s1 t1
s2 t2
s3 t3
T2
s x y
s1 x1 y1
s2 x1 y2
s1 x2 y3
s3 x2 y4
And I want to get the following data by joining T1 and T2.
t1 x1 y1
t2 x1 y2
t3 x1 0
t1 x2 y3
t2 x2 0
t3 x2 y4
How can I get it?
[Updated on: Sun, 13 January 2008 07:28] Report message to a moderator





Re: Difficulty in joining two tables! [message #293494 is a reply to message #293489] 
Sun, 13 January 2008 08:21 
chepi3000
Messages: 5 Registered: January 2008 Location: CHONGQING

Junior Member 


Michel Cadot wrote on Sun, 13 January 2008 06:42  You have to explain in words what is the rule and not just produce an output.
Where does come t3/0 and t2/0?
Regards
Michel

Thank you again ,Michel!
I can get the answer by the statement below.But I think it a little compicated!
select t1.t,t2.x,nvl(t2.y,0)
from t1,t2,(select distinct x from t2) t3
where t1.s=t2.s(+)
and t3.x=t2.x(+)



Re: Difficulty in joining two tables! [message #293495 is a reply to message #293491] 
Sun, 13 January 2008 08:21 
Frank Naude
Messages: 4507 Registered: April 1998

Senior Member 


This should work:
SQL> SELECT t, 'x1', nvl(y, '0') FROM t1, t2 WHERE t1.s = t2.s(+) and x(+) = 'x1'
2 UNION
3 SELECT t, 'x2', nvl(y, '0') FROM t1, t2 WHERE t1.s = t2.s(+) and x(+) = 'x2';
T 'X NV
  
t1 x1 y1
t1 x2 y3
t2 x1 y2
t2 x2 0
t3 x1 0
t3 x2 y4
6 rows selected.




Re: Difficulty in joining two tables! [message #293499 is a reply to message #293496] 
Sun, 13 January 2008 09:32 
chepi3000
Messages: 5 Registered: January 2008 Location: CHONGQING

Junior Member 


Michel Cadot wrote on Sun, 13 January 2008 08:48  Don't change the post and the question after we replied to it. Correct in a nex post.
You still does not explain the rule.
Why t3 x1 0 and t2 x2 0? What happens if you add 's2 x3 y5' or 's2 x2 y5' or 's4 x2 y1' or...?
Regards
Michel

Hi,Michel
I'm showing my sql which is a little complex.
T1
s t
s1 t1
s2 t2
s3 t3
T2
s x y
s1 x1 y1
s2 x1 y2
s1 x2 y3
s3 x2 y4
result:
t1 x1 y1
t2 x1 y2
t3 x1 0
t1 x2 y3
t2 x2 0
t3 x2 y4
This sql statement works:
select tt.s,tt.x,nvl(t2.y,0) y from
(select * from t1,(select distinct x from t2)) tt,
t2
where tt.s=t2.s(+)
and tt.x=t2.x(+)
But I want to have a simpler one,any suggestion!
Thank you in advance!




Re: Difficulty in joining two tables! [message #293526 is a reply to message #293502] 
Sun, 13 January 2008 23:09 
chepi3000
Messages: 5 Registered: January 2008 Location: CHONGQING

Junior Member 


Michel Cadot wrote on Sun, 13 January 2008 10:33  You did'nt to my question about the new rows.
You have to explain IN WORDS what is the rule.
Regards
Michel

Hi,Michel!Thank you again.
Let me explain it like this:
In table T2,we group it by column X,then we get two groups x1,and x2,then we have a natural join on T1 and the grouped X,as shown below:
temp
s X t
s1 x1 t1
s2 x1 t2
s3 x1 t3
s1 x2 t1
s2 x2 t2
s3 x2 t3
If we have more groups(by X) in T2,such as x3,x4,we get the data shown below:
temp
s X t
s1 x1 t1
s2 x1 t2
s3 x1 t3
s1 x2 t1
s2 x2 t2
s3 x2 t3
s1 x3 t1
s2 x3 t2
s3 x3 t3
s1 x4 t1
s2 x4 t2
s3 x4 t3
Then we let temp left join T2 on temp.s=T2.s and temp.x=T2.X
Thank you in advance.
[Updated on: Sun, 13 January 2008 23:13] Report message to a moderator



Re: Difficulty in joining two tables! [message #293548 is a reply to message #293526] 
Mon, 14 January 2008 01:04 

Michel Cadot
Messages: 64726 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Specifications MUST not refer to any data. It must explain what we must have for any data.
Try to put your specifications WITHOUT refering to x1, x2 and so on.
AFTER that, you can explain how the rule applies to your example to generate the ouput.
You have to understand that SQL is the easiest part once you have clean the specifications and have clear specifications.
Regards
Michel
[Updated on: Mon, 14 January 2008 01:05] Report message to a moderator



Re: Difficulty in joining two tables! [message #293552 is a reply to message #293488] 
Mon, 14 January 2008 01:17 
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi

Senior Member 


SQL> ed
Wrote file afiedt.buf
1 select temp1.t,temp1.x,nvl(temp2.y,0)
2 from ( select distinct t1.t,t2.x from t1,t2)temp1,
3 (select t1.t,t2.x,t2.y from t1,t2
4 where t1.s=t2.s)temp2
5 where temp1.t=temp2.t(+)
6 and temp1.x=temp2.x(+)
7* order by temp1.x,temp1.t
SQL> /
T X NV
  
t1 x1 y1
t2 x1 y2
t3 x1 0
t1 x2 y3
t2 x2 0
t3 x2 y4
6 rows selected.



Goto Forum:
Current Time: Thu Apr 27 06:05:18 CDT 2017
Total time taken to generate the page: 0.12571 seconds
