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 Go to next message
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 #293489 is a reply to message #293488] Sun, 13 January 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Difficulty in joining two tables! [message #293491 is a reply to message #293489] Sun, 13 January 2008 07:11 Go to previous messageGo to next message
chepi3000
Messages: 5
Registered: January 2008
Location: CHONGQING
Junior Member
Thank you,Michel!
I wrongly described the situation.
Let me divide table T2 into two groups:
group1:
s x y
s1 x1 y1
s2 x1 y2
group2:
s x y
s1 x2 y3
s3 x2 y4

[Updated on: Sun, 13 January 2008 07:31]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 #293496 is a reply to message #293494] Sun, 13 January 2008 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

Re: Difficulty in joining two tables! [message #293499 is a reply to message #293496] Sun, 13 January 2008 09:32 Go to previous messageGo to next message
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 #293502 is a reply to message #293499] Sun, 13 January 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did'nt to my question about the new rows.
You have to explain IN WORDS what is the rule.

Regards
Michel
Re: Difficulty in joining two tables! [message #293526 is a reply to message #293502] Sun, 13 January 2008 23:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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 Go to previous message
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.
Previous Topic: Sequence incementation
Next Topic: To find the available time intervals for a day
Goto Forum:
  


Current Time: Mon Dec 05 23:56:13 CST 2016

Total time taken to generate the page: 0.10119 seconds