Mega SQL command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mega SQL command

Author

Topic

goppi
Starting Member

6 Posts

Posted - 2008-09-17 : 03:45:30

Hi,

I'm struggeling with a SQL command for a report. The difficulty is that there are 13 tables involved and tables can be linked together in different ways. F.i. A single order has a direct 1:n link to executions, however an order can also be part of a Parent order - in this case the executions would be linked to the child orders and the child orders to the parent order. In total I have 8 different ways of linking the tables together. I initially build 4 separate SQL commands based on joined tables and linked them together with a UNION operator, but the query took ages. I than change each SQL statement from joined tables to a "select a,b,c from table1,table2 where table1.x=table2.x"- structure. It worked but was heavy to maintain and still slow. When I extended the whole statement to 8 SQL statements, it was just so difficutl to maintain that I tried to merge the UNION SQL statements to a form such as "select a,b,c from table1, table2, table3 where table1.x=table2.x and ((table2.y=table3.x) or (table2.y=table3.x))" - but it takes ages now again.

What is the best (in terms of fast execution) to build such a complicated statement?

cheers,
Goppi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 03:48:56

Show some samples from your tables with struture and illustrate what report data you want.

goppi
Starting Member

6 Posts

Posted - 2008-09-17 : 04:03:47

quote:
Originally posted by visakh16

Show some samples from your tables with struture and illustrate what report data you want.

I will try to simplify the whole thing. The relevant tables are:

Bookings
Orders
Order_Details
Executions

If I get the right way for the 4 linked variations I should be able to expand it to 8 as well - so for now just the 4 variations:

1. Variation:
Bookings.Order_ID = Orders.ID
Orders.ID = Order_Details.ID
Orders.ID = Executions.Order_ID

2. Variation:
Bookings.Order_ID = Orders.ID
Orders.ID = Order_Details.ID
Orders.ID = Executions.Parent_Order_ID

3. Variation:
Bookings.Order_ID = Order_Details.Order_Group_ID
Orders.ID = Order_Details.ID
Orders.ID = Executions.Order_ID

4. Variation:
Bookings.Order_ID = Order_Details.Order_Group_ID
Orders.ID = Order_Details.ID
Orders.ID = Executions.Parent_Order_ID

The result should be a merge of the 4 Variations.

Does this make sense?

goppi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 04:08:31

so you want to join to same table based on more than one column values?

goppi
Starting Member

6 Posts

Posted - 2008-09-17 : 04:12:33

quote:
Originally posted by visakh16

so you want to join to same table based on more than one column values?

Correct, the thing is that with every new type of business the amount of variations goes up exponentially. Thats' the reason why I need to find another solution than to have 4/8 SQL statements joined together with UNION.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 05:57:40

quote:
Originally posted by goppi

quote:
Originally posted by visakh16

so you want to join to same table based on more than one column values?

Correct, the thing is that with every new type of business the amount of variations goes up exponentially. Thats' the reason why I need to find another solution than to have 4/8 SQL statements joined together with UNION.

why is system designed like this?

goppi
Starting Member

6 Posts

Posted - 2008-09-17 : 06:06:03

quote:
Originally posted by visakh16

quote:
Originally posted by goppi

quote:
Originally posted by visakh16

so you want to join to same table based on more than one column values?

Correct, the thing is that with every new type of business the amount of variations goes up exponentially. Thats' the reason why I need to find another solution than to have 4/8 SQL statements joined together with UNION.

why is system designed like this?

It's the database for a trading system and from that point in control of a third party.

goppi
Starting Member

6 Posts

Posted - 2008-09-17 : 08:15:26

Does anybody have an idea how to solve this problem?

Thanks,
Goppi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 09:42:32

Try this

SELECT		*
FROM		Orders AS o
INNER JOIN	Order_Details AS od ON od.ID = o.ID
CROSS JOIN	Bookings AS b
CROSS JOIN	Executions AS e
WHERE		b.Order_ID = o.ID AND o.ID = e.Order_ID
		OR b.Order_ID = o.ID AND o.ID = e.Parent_Order_ID
		OR b.Order_ID = od.Order_Group_ID AND o.ID = e.Parent_Order_ID
		OR b.Order_ID = od.Order_Group_ID AND o.ID = e.Parent_Order_ID

E 12°55'05.63"
N 56°04'39.26"

goppi
Starting Member

6 Posts

Posted - 2008-09-18 : 08:58:34

quote:

Originally posted by Peso

Try this

SELECT		*
FROM		Orders AS o
INNER JOIN	Order_Details AS od ON od.ID = o.ID
CROSS JOIN	Bookings AS b
CROSS JOIN	Executions AS e
WHERE		b.Order_ID = o.ID AND o.ID = e.Order_ID
		OR b.Order_ID = o.ID AND o.ID = e.Parent_Order_ID
		OR b.Order_ID = od.Order_Group_ID AND o.ID = e.Parent_Order_ID
		OR b.Order_ID = od.Order_Group_ID AND o.ID = e.Parent_Order_ID

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso - I'll try it - just for my uneducated understanding - wouldn't it work as well with just INNER JOINS instead of CROSS JOINS?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources