Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help (12.2.0.1)
SQL Help [message #673342] Thu, 15 November 2018 18:42 Go to next message
kapilavastu
Messages: 9
Registered: July 2015
Junior Member
I have a data set like below.

Table 1

unit            item
----------------------------

S1               I1
S1               I2
S1               I3

Table 2

item           substitute_item    createdon

I3                 I7               Jan2018
I8                 I3               Feb2018
I9                 I8               Mar2018

we can read above as  i7 was replaced with i3 and i3 was replaced with i8 and i8 was replaced with i9.

I would like my result set to have

S1        I1
S1        I2
S1        I3
S1        I7
S1        I8
S1        I9

Thanks for your help.



Re: SQL Help [message #673347 is a reply to message #673342] Fri, 16 November 2018 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 22 August 2015 07:03

Michel Cadot wrote on Wed, 29 July 2015 20:20

Also, if you want to continue to get help you should feedback, thanks those who spend time to help you and answer their questions.
Review your previous topic.
If you got a solution then post it.
We are waiting on this.
...
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.


Re: SQL Help [message #673355 is a reply to message #673347] Fri, 16 November 2018 01:57 Go to previous messageGo to next message
zhaoquer
Messages: 35
Registered: October 2013
Member
kapilavastu ,
I can't find any relationship between S1 and (I8,I9) , can you explain?
Re: SQL Help [message #673359 is a reply to message #673355] Fri, 16 November 2018 03:33 Go to previous messageGo to next message
cookiemonster
Messages: 13382
Registered: September 2008
Location: Rainy Manchester
Senior Member
From the description it's the other way around - there's no relationship to I7.

I3 was replaced with I8.
I8 was replace with I9.

So I3 becomes I8 and then I9.

But

I7 was replaced with I3

I7 doesn't appear in table 1.

@kapilavastu - you need to explain in more detail how you get that result set from that data, as well as supplying all the other stuff Michel has asked for.
Re: SQL Help [message #673693 is a reply to message #673359] Mon, 03 December 2018 23:12 Go to previous messageGo to next message
kapilavastu
Messages: 9
Registered: July 2015
Junior Member
Sorry for this late email.

To explain this in an easy example.

Table 1

I1
I2
I3
I7
I8
I9


I1,I2,I9 are linked to S1 through a derived relationship by joining multiple tables.


item substitute_item createdon

I3 I7 Jan2018
I8 I3 Feb2018
I9 I8 Mar2018


I9 was replaced by I8 and I8 was replaced by I3 and I3 was replaced by I7.

I would like the result set to be like

I1 S1
I2 S1
I3 S1
I7 S1
I8 S1
I9 S1


Re: SQL Help [message #673695 is a reply to message #673693] Tue, 04 December 2018 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 16 November 2018 07:48
Michel Cadot wrote on Sat, 22 August 2015 07:03

Michel Cadot wrote on Wed, 29 July 2015 20:20

Also, if you want to continue to get help you should feedback, thanks those who spend time to help you and answer their questions.
Review your previous topic.
If you got a solution then post it.
We are waiting on this.
...
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.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Re: SQL Help [message #673699 is a reply to message #673693] Tue, 04 December 2018 08:07 Go to previous message
joy_division
Messages: 4917
Registered: February 2005
Location: East Coast USA
Senior Member
Why would you expect repeating the same gibberish a month later will get you a better response. A better well though out, coherent question with a test case will get you an answer, not this.
Previous Topic: How to use Optimistic locking in my Package
Next Topic: Fill in missing data
Goto Forum:
  


Current Time: Thu Dec 13 04:31:02 CST 2018