Home » SQL & PL/SQL » SQL & PL/SQL » Maybe an impossible join
Maybe an impossible join [message #269246] Fri, 21 September 2007 06:17 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

I have got a question on a sql join

I have the two Tables

Table1:

Column1__ Column 2__ Column 3
Hallo1_____AAA______Ja1
Hallo2_____AAA______Ja2
Hallo3_____AAA______Ja3
Hallo4_____BBB______Ja4
Hallo5_____BBB______Ja5


Table2:

Column1__ Column 2__ Column 3
Wert1_____11111____BMW1
Wert2_____11111____BMW2
Wert3_____11111____BMW3
Wert4_____22222____BMW4
Wert5_____22222____BMW5


I really do not know if this works but I want to join both tables on the 2nd Column.
I want to match the AAA Datasets from Table1 to the 11111 Datasets from Table2. And also the BBB Datasets from Table1 to the 22222 Datasets from Table2.

Is it possible to join columns with different values by telling the database which values in the columns it should match against each other

Thanks in advance for you help.
Re: Maybe an impossible join [message #269250 is a reply to message #269246] Fri, 21 September 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Now explain why 11111 and AAAA, 22222 and BBBB and so on. What is the logic?

Regards
Michel
Re: Maybe an impossible join [message #269266 is a reply to message #269246] Fri, 21 September 2007 07:21 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The only way I can think of to do what you want would be to use a mapping table to translate the '1111' to 'AAA' etc. If there are only a very small number of values, you could join using a decode or case statement, but it would be very limiting.

[Updated on: Fri, 21 September 2007 07:21]

Report message to a moderator

Re: Maybe an impossible join [message #269274 is a reply to message #269266] Fri, 21 September 2007 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways to do it depending on the logic behind.

Regards
Michel
Re: Maybe an impossible join [message #269280 is a reply to message #269246] Fri, 21 September 2007 08:08 Go to previous messageGo to next message
galaxy
Messages: 62
Registered: October 2005
Member
It is an oracle 9i database. I have only select rights on these tables and I cannot produce a translation table.

So I need to do this in a select statement. The two columns I want to match are both string columns.(The 11111 and 22222 was not so well chosen because this column is also string)

And I only need this match for 3 different combinations. So that is not the problem if this is limiting.

But I do not know how I can match the differnt columns with different values.

What I need is something like this: (This is like Pseudocode)

select Table1.Spalte1,Table1.Spalte2,table2.Spalte1 from table1,table2
where Table1.Spalte2=Table2.Spalte2(for AAA=11111 and BBB=22222)

But I do not know how to create this where clause. Maybe it works with decode but I do not know how!
Re: Maybe an impossible join [message #269287 is a reply to message #269280] Fri, 21 September 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you only have 3 combinations then do:
where ( a.col='1111' and b.col='AAAA' ) or ( a.col = '2222' and b.col = 'BBBB' ... )

Regards
Michel
Re: Maybe an impossible join [message #269357 is a reply to message #269246] Fri, 21 September 2007 16:09 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try

select Table1.Spalte1,Table1.Spalte2,table2.Spalte1
from table1,table2
where 
  DECODE(Table1.Spalte2, 'AAA', 11111,
                         'BBB', 22222,
                         'CCC',33333) = Table2.Spalte2


I'm NOT responsible for performance.

Michael
Re: Maybe an impossible join [message #269403 is a reply to message #269357] Sat, 22 September 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm NOT responsible for performance

Yes, better enumerate all conditions to allow the optimizer to choose the indexes.

Regards
Michel
Re: Maybe an impossible join [message #269405 is a reply to message #269357] Sat, 22 September 2007 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If this is indeed the only combination of possibilities, you could create a function based index on the decode.
Re: Maybe an impossible join [message #269411 is a reply to message #269405] Sat, 22 September 2007 01:55 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indexes was just an example.
I could say and let the optimizer know the row numbers distribution within each used value.

Regards
Michel
Previous Topic: How to use UTL_FILE to export to flat file?
Next Topic: How to list records in oracle/sql?
Goto Forum:
  


Current Time: Sun Dec 04 22:40:33 CST 2016

Total time taken to generate the page: 0.27336 seconds