Home » SQL & PL/SQL » SQL & PL/SQL » Alternative syntax for Self joining of a table 3 times (ORacle 9i,Windows 2000)
Alternative syntax for Self joining of a table 3 times [message #361870] Fri, 28 November 2008 05:26 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi, I have written a query which performs a self join of one table.I couldnt think of a different way so I am self joining it 3 timesBut this query hangs now.
The relation might seem a bit complicated, but I basically want to know if this could be done in a different syntax,but to get exactly the same result?
t_localities table l2 is only for identifying the secondary locality name for the secondary locality in the example(locality id=59)

Any help/ideas are much much appreciated
SELECT DISTINCT b.building_id building_id, 
                l.locality_id old_loc_id,
		b.county_id county_id,
	        l1.locality_id new_loc_id,
		l1.name loc_name,
		l2.locality_id sec_loc_id,
		l2.county_id sec_loc_county_id,
	        l2.name sec_loc_name
	   FROM T_BUILDINGS b, 
	   T_URBAN_AREAS ua, 
	   T_LOCALITIES l, 
	   T_LOCALITIES l1,
	   T_LOCALITIES l2,
	   T_POST_TOWNS p,
	   T_ROUTES R,
	   T_DELIVERY_OFFICES D
	WHERE b.urban_area_id=ua.urban_area_id
	AND b.invalid ='N'
	AND b.urban_area_id > 27
	AND b.route_id=r.route_id
	AND b.locality_id=l.locality_id
	AND r.delivery_office_id=d.delivery_office_id
	AND d.post_town_id = p.post_town_id
	AND b.thorfare_id > 0
	AND ua.name=p.name
	AND p.name <> l.name
	AND p.name =l2.name
	AND b.county_id=l1.county_id
	AND l.county_id=l2.county_id
	AND l.county_id =l1.county_id
	AND l.name=l1.name
	AND l.secondary_locality_id IS NULL
	AND l.locality_type_id<>59
	AND l1.locality_type_id <>59
	AND l2.locality_type_id=59
	AND r.delivery_office_id IN(SELECT delivery_office_id FROM T_DELIVERY_OFFICES WHERE auto_inward='Y')
	AND l1.secondary_locality_id=l2.locality_id
	AND l1.name !=l2.name;

Re: Alternative syntax for Self joining of a table 3 times [message #361883 is a reply to message #361870] Fri, 28 November 2008 06:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Without you posting some test data to play with it and the expected output it will be difficult for anybody to answer your question.

Regards

Raj
Re: Alternative syntax for Self joining of a table 3 times [message #361932 is a reply to message #361883] Fri, 28 November 2008 11:01 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I just posted this in a hurry, to see if I get any valuable suggestions...but not too worry now, as I figured a way out myself.
Re: Alternative syntax for Self joining of a table 3 times [message #361944 is a reply to message #361932] Fri, 28 November 2008 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aviana wrote on Fri, 28 November 2008 18:01
I just posted this in a hurry, to see if I get any valuable suggestions...but not too worry now, as I figured a way out myself.

Great! next time do it before posting this, completly useless for others.

Regards
Michel

Re: Alternative syntax for Self joining of a table 3 times [message #361947 is a reply to message #361870] Fri, 28 November 2008 12:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It is good that you found a solution for yourself. Please, finish this post, show us your solution. That way, this thread has a conclusion that others can benefit from.

Kevin
Re: Alternative syntax for Self joining of a table 3 times [message #362146 is a reply to message #361947] Mon, 01 December 2008 03:31 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi Kevin you are right that I should share the solution, then only this post will be meaningful..
Sorry Michel,I was not sure I would be able to reach a better solution thats why I did this post, in the first place.
Solution was:
Joining tables thrice worked but it took around 2 and half hours to run..What I did was self joining the tables only twice but separted it as 2 different queries (ie, one with l and l1 and another query with l1 and l2) .Created 2 temporary tables and compared the values in a way which I need..It took only around 45 minutes.Hope this helps.
Re: Alternative syntax for Self joining of a table 3 times [message #362148 is a reply to message #362146] Mon, 01 December 2008 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If creating temporary tables is quicker than running the query, then something is amis.

what indexes do you have on the Localities table?
Re: Alternative syntax for Self joining of a table 3 times [message #362171 is a reply to message #362148] Mon, 01 December 2008 04:31 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Why?Is creating a temp table supposed to be slower?
Regarding indexes, I have unique index on locality_id and non unique indexes on fields name, county id and secondary locality id
Re: Alternative syntax for Self joining of a table 3 times [message #362186 is a reply to message #362171] Mon, 01 December 2008 05:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Almost always slower, unless your query is doing something daft.

Can you post an explain plan for the execution of the original query?

Also - why have you got a DISTINCT in the original query - that's usually a sign that there's a join condition missing and you've not been able to restrict thr data set to individual rows.

[fixed schoolboy error in grammar]

[Updated on: Mon, 01 December 2008 05:19]

Report message to a moderator

Previous Topic: Table Analysis
Next Topic: Problem in execution of a function having clob datatype
Goto Forum:
  


Current Time: Sun Dec 11 08:32:14 CST 2016

Total time taken to generate the page: 0.11939 seconds