Home » SQL & PL/SQL » SQL & PL/SQL » Get data from more tables which have no direct relationship
Get data from more tables which have no direct relationship [message #185224] Mon, 31 July 2006 11:16 Go to next message
dhanisetti
Messages: 7
Registered: July 2006
Junior Member
Hi Experts,

I want to get the data from more than 3 tables but there is no direct relationship among those tables so that i have to connect more than 5 tables to get the data.

I used the following query to get the same but it shows incorrectly(ofcourse my query is wrong)

Note: I used DISTINCT keyword in TABLE6 as it contains duplicates.

select a.COLUMN1,b.COLUMN1,c.COLUMN1 from TABLE1 a,TABLE2 b,TABLE3 c where a.COLUMN2 in (select COLUMN2 from TABLE4 where COLUMN1 in (select COLUMN1 from TABLE5 where COLUMN2 in (select DISTINCT COLUMN2 from TABLE6 where COLUMN1 in (select COLUMN1 from TABLE2))))

Could you please help me on this??

Thanks in advance
Dhani

Re: Get data from more tables which have no direct relationship [message #185229 is a reply to message #185224] Mon, 31 July 2006 11:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why didn't you simply join all 6 tables? No matter the fact that you need data from tables A, B and C - include all 6 of them into the FROM clause and join necessary columns in the WHERE clause; there will be minimum 5 joins, but could also be some more if keys are made of more than one column.
Re: Get data from more tables which have no direct relationship [message #185240 is a reply to message #185229] Mon, 31 July 2006 12:46 Go to previous messageGo to next message
dhanisetti
Messages: 7
Registered: July 2006
Junior Member
Thanks Littlefoot for your prompt reply.

I tried in the way you said i.e. i mentioned all the table names in FROM caluse but I got the same output as earlier.

Could you please give me possible reasons why my query is not giving required result and let me know if you any alternative to get so.

Thanks
Dhani
Re: Get data from more tables which have no direct relationship [message #185257 is a reply to message #185240] Mon, 31 July 2006 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, there are experts here who would perhaps see obvious (some of them are on vacation; bad luck for you!), but I don't. Would it be possible for you to prepare a script - CREATE TABLE, INSERT sample data statements and expected result?
Re: Get data from more tables which have no direct relationship [message #185271 is a reply to message #185224] Mon, 31 July 2006 20:59 Go to previous messageGo to next message
ruyue
Messages: 9
Registered: July 2006
Junior Member
Why not just directly join them ?You should have 5 conditions to join 6 tables at least.
I have not found tablec in your statement.It will return Cartesian.

[Updated on: Tue, 01 August 2006 04:36]

Report message to a moderator

Re: Get data from more tables which have no direct relationship [message #185284 is a reply to message #185271] Tue, 01 August 2006 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Descartes? Smile Good one, I like it, although people usually use Cartesian product term.
Re: Get data from more tables which have no direct relationship [message #185288 is a reply to message #185224] Tue, 01 August 2006 00:44 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

try this one...
Not sure, as i dont know how the data looks like...
select table1.column1,a.c1 
  from (select DISTINCT table6.COLUMN2 c2 , table2.column1 c1 
          from table6 , Table2
         where table6.column1 = table2.column1)a,
       table5,
	   table4, 
	   table1
 where table1.column2 = table4.column2
   and table4.column1 = table5.column1
   and table5.column1 = a.c2  



In the above query i havent used table3 since i am not sure of its joining table.

Naveen

Re: Get data from more tables which have no direct relationship [message #185331 is a reply to message #185224] Tue, 01 August 2006 04:37 Go to previous messageGo to next message
ruyue
Messages: 9
Registered: July 2006
Junior Member
Hi Littlefoot, I corrected it, thanks.

[Updated on: Tue, 01 August 2006 04:37]

Report message to a moderator

Re: Get data from more tables which have no direct relationship [message #185349 is a reply to message #185224] Tue, 01 August 2006 05:22 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi,
as LittleFoot correctly points out, as does Naveen, you will get a much quicker (and possibly better) answer if you supply some sample scripts to create the tables and insert some representative sample data.

Jim
Re: Get data from more tables which have no direct relationship [message #185414 is a reply to message #185224] Tue, 01 August 2006 11:20 Go to previous message
dhanisetti
Messages: 7
Registered: July 2006
Junior Member
Thanks to everybody.

I got the required results with all of your suggestions.

Regards
Dhani
Previous Topic: Changing two clauses into one
Next Topic: creating table in procedure
Goto Forum:
  


Current Time: Sat Dec 10 03:25:59 CST 2016

Total time taken to generate the page: 0.08905 seconds