Solved

Changing the join order

Posted on 2008-10-28
17
385 Views
Last Modified: 2010-04-21
I'm developing a front end query utility that let's users build queries off of a model I define.  I have it creating the query fine except for one catch, the join order is sometimes out of order and the query won't run (if I move one of the joins up two lines, it works fine).  I obviously know the order I should put them in when I manually write it but I never know the combintion of tables a user will pick.  Question:

Is there a way to make SQL Server re-order my joins for me?

Oddly enough, if I write my SQL with old non ANSI joins, the SQL Server does choose the join order and the query works fine.  I would much prefer to use ANSI joins as opposed to (select * from table1, table2, table3 where table1.id = table2.id and table2.otherfield = table3.otherfield).
0
Comment
Question by:IUFITS
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22826859
What is your intended result?  If you are wanting to get only records that have values in all three tables then:

select *
from table1 t1 inner join table2 t2
on t1.id = t2.id
inner join table3 t3
on t2.otherfield = t3.otherfield

If you want everything from table2 since it has fields for both, you do this:

select *
from table2 t2 left join table2 t1
on t1.id = t2.id
left join table3 t3
on t2.otherfield = t3.otherfield

As you can see the order depends entirely on your desired output as can make one an inner join and one a left join.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 22826885
Here's an example, it fails because the transaction_detail join needs to appear before the transaction_person join.  I get an error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "transaction_detail.transaction_id" could not be bound.

When I move that join up above the transaction_person join, it works.  The non ANSI sql doesn't matter, I can order them however I want and it runs (I assume the SQL Server choose the join order appropriately).  Is there a way to force the join order with ANSI joins?
SELECT account_number.account_number,

account_number.account_name,

transaction_detail.transaction_amount,

transaction_detail.credit_date,

transaction_person.credit_amount,

FROM account_number 

Inner Join transaction_person on transaction_person.transaction_id = transaction_detail.transaction_id 

Inner Join person On transaction_person.person_id = person.person_id 

Inner Join transaction_detail On transaction_detail.account_number = account_number.account_number  

Open in new window

0
 
LVL 3

Author Comment

by:IUFITS
ID: 22826894
Err I meant, is there a way to force SQL Server to re-order the joins appropriately since this is all being generated dynamically?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22826972
That is an interesting one, don't think it can be done or at least not easily.  Hopefully you will get some other postings, but since there are a couple entries in now experts may not think to look in the question so if you don't hear anything in a little bit you can use the methods explained in http:/help.jsp to get some further assistance.

Maybe it is simple and having one of those moments...

Good luck!
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 22827004
the key is in the " since this is all being generated dynamically? "

your "generator" has to be aware of the different joining conditions that are possible.
either "automatically" via the foreign keys, but there are problems when tables are related multiple times,
or, via configuration tables, describing the links, so that when you "add" a table to your query, you can only add it based on another table already in the "list", and with the join condition already prepared.

hope this helps
0
 
LVL 3

Author Comment

by:IUFITS
ID: 22827042
That's basically what I'm doing via an XML config file.  I have the joins pre-defined to get from table to table.  It's finding them and including all of them in the query, just not in the right order.  It's trying to join something that doesn't get joined until two ansi joins down and I'm not sure how to make it know which to put first dynamically (which is why I was hoping there was a way to force the SQL Server to optimize them).  

Below are examples, the top one works, the bottom one doesn't.  I'm struggling with getting the order right on the fly.
-- Not working query

SELECT account_number.account_number,

account_number.account_name,

transaction_detail.transaction_amount,

transaction_detail.credit_date,

transaction_person.credit_amount,

FROM account_number 

Inner Join transaction_person on transaction_person.transaction_id = transaction_detail.transaction_id 

Inner Join person On transaction_person.person_id = person.person_id 

Inner Join transaction_detail On transaction_detail.account_number = account_number.account_number  
 

-- Working query

SELECT account_number.account_number,

account_number.account_name,

transaction_detail.transaction_amount,

transaction_detail.credit_date,

transaction_person.credit_amount,

FROM account_number 

Inner Join transaction_detail On transaction_detail.account_number = account_number.account_number  

Inner Join transaction_person on transaction_person.transaction_id = transaction_detail.transaction_id 

Inner Join person On transaction_person.person_id = person.person_id 

Open in new window

0
 
LVL 3

Author Comment

by:IUFITS
ID: 22827061
Like I mentioned also, the non-ansi joins do the trick, the SQL Server re-orders them itself... now, since it's outdated syntax and MS says not to be supported indefintedly I'm wearing to use it... here's an example of that which works:
SELECT account_number.account_number,

account_number.account_name,

person.last_name,

person.first_name,

transaction_detail.transaction_amount,

transaction_detail.credit_date,

transaction_person.credit_amount

FROM account_number, transaction_person, person, transaction_detail, unit_code

WHERE transaction_person.transaction_id = transaction_detail.transaction_id 

and transaction_person.person_id = person.person_id and transaction_detail.account_number = account_number.account_number  

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22827193
How is the XML setup?

<table name="transaction_person">
    <join tablename="transaction_detail">
        <join-field local="transaction_id" target="transaction_id"/>
    </join>
</table>

If close to something like that, you could try to make sure that the dynamic query join for transaction_person happens after xml element table with @name = transaction_detail.  

Trying to think out loud, hopefully not too confusing.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Author Comment

by:IUFITS
ID: 22827289
Yes, that's very similiar to what I've tried.  Since I'm just hasing it out I actually included the join syntax in my XML just to get a prototype running, here's some of the syntax (I stripped a lot of it out of this:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<DocumentElement>

  <Table value="account_number" friendlyName="Account">

    <Field value="account_number"></Field>

    <Field value="account_name"></Field>

    <Link tableName="transaction_detail" sqlJoin=" Inner Join account_number On account_number.account_number = transaction_detail.account_number "></Link>

    <Link tableName="unit_code" sqlJoin=" Inner Join account_number on account_number.unit_code = unit_code.unit_code "></Link>

    <Link tableName="area_of_giving_code" sqlJoin=" Inner Join account_number On account_number.area_of_giving_code = area_of_giving_code.area_of_giving_code "></Link>

  </Table>

  <Table value="person" friendlyName="Person">

    <Field value="person_id"></Field>

    <Field value="preferred_name"></Field>

    <Field value="last_name"></Field>

    <Field value="first_name"></Field>

    <Field value="middle_name"></Field>

    <Link tableName="person_giving" sqlJoin=" Inner Join person On person_giving.person_id = person.person_id "></Link>

    <Link tableName="transaction_person" sqlJoin=" Inner Join person On transaction_person.person_id = person.person_id "></Link>

    <Link tableName="transaction_detail" sqlJoin=" Inner Join transaction_person on transaction_person.transaction_id = transaction_detail.transaction_id Inner Join person On transaction_person.person_id = person.person_id "></Link>

  </Table>

  <Table value="transaction_detail" friendlyName="Transaction Detail">

    <Field value="transaction_id"></Field>

    <Field value="transaction_amount"></Field>

    <Field value="credit_date"></Field>

    <Field value="payment_method_code"></Field>

    <Field value="account_number"></Field>

    <Link tableName="person" sqlJoin=" Inner Join transaction_person On transaction_person.person_id = person.person_id Inner Join transaction_detail On transaction_person.transaction_id = transaction_detail.transaction_id "></Link>

    <Link tableName="transaction_person" sqlJoin=" Inner Join transaction_detail On transaction_person.transaction_id = transaction_detail.transaction_id "></Link>

    <Link tableName="account_number" sqlJoin=" Inner Join transaction_detail On transaction_detail.account_number = account_number.account_number "></Link>

  </Table>

</DocumentElement>    

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 50 total points
ID: 22827455
As you are building this then, you can use a List<String> (presuming you are using .NET to do the dynamic portion) to store already used tables.  When you have a new join, you can test to make sure the tableName attribute value like "transaction_detail" from link element already exists in the list.  If it doesn't, you can process that table first.  Easier said than done, but hopefully that helps get you started.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22827462
Think it needs to happen on the frontend is the point and just send correct SQL to database.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22827471
I'll just throw this in because I've built something like this before.  What you need to do is store the relationships between objects (tables) in a structure that defines how they are related.  You can then reference that to ensure that you don't try to join TableA to TableB before TableB has been added to the query.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 22827696
I did something similiar to the List<String> but instead I put a TableDefinition object I created into it that was able to store more detail, basically everything in the XML plus a few booleans like IsUsed, IsLinked and I was playing around with populating a JoinOrder variable, the logic which I'm trying to work through.  BrandonGalderisi's thoughts are where I'm trying to go I think, but I'm not sure what type of relationship data to store to track the hierchey down the chain.  
It would be considerably easier if the base table didn't change.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 400 total points
ID: 22828163
Well IDEALLY each of your tables will have a single field primary key.  Then you can store it in a simple relationship table

create table dbo.table_relationships
 (table_relationships_id int identity(1,1) not null
 ,table_1  nvarchar(255) not null
 ,field_1    nvarchar(255) not null
 ,table_2  nvarchar(255) not null
 ,field_2    nvarchar(255) not null
 ,constraint pk_table_relationships primary key clustered (table_relationships_id)
 ,constraint uidx_table_Relationships_all (table_1,field_1,table_2,field_2)
)
insert into dbo.table_relationships (table_1,field_1,table_2,field_2) values ('person','person_id','transaction','person_id')

Note that I'm not calling it parent child table.  Because they could be sibling relationships and not hierarchical data.  This prevents the user from joining tables that YOU haven't defined as valid because when they select the 'Person' table, the only option they have is tables that you define are valid to be joined to person.  And let's say that your transaction table has created and modified information:

insert into dbo.table_relationships (table_1,field_1,table_2,field_2) values ('person','person_id','transaction','modified_person_id')
insert into dbo.table_relationships (table_1,field_1,table_2,field_2) values ('person','person_id','transaction','created_person_id')

Now, when the user chooses person and transaction, they have to decide WHAT relationship between the two they want to use.  This is the reason for my next suggestion which is aliases.  You should be providing table aliases in your BUILT query so that multiple references to the same table are unique.

Just don't get caught up on it being a hierarchy.  It's not.  It's defining the relationships between database objects in a way that allows your code to present the user options for joining the tables.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 22828372
That makes a lot of sense.  I think this advice might have just helped it click for me, because I was focusing too much on it being a hierachy:
"Just don't get caught up on it being a hierarchy.  It's not.  It's defining the relationships between database objects in a way that allows your code to present the user options for joining the tables."
I'll change the way the relationship is stored from straight SQL to field stores (I'm dealing with single primary keys).  After that, I think what I need to do is start with the base table and work out on what the user selects... something like:
  1. Get the base table
  2. Gather the tables a user has selected criteria from
  3. I don't know the term, but loop outwards joining any selected tables that can be joined to the base table, then continue out until everything's joined or an error is tossed from a table not being joined that's included.
I'm going to sleep on that and give it a try tomorrow.  I think what's provided has put me in the right direction.  I'll be back to accept the solution tomorrow.  If I get this worked out I think I'm going to share it on CodePlex.  :P  
Thanks again, I can't tell you how meaningful and worthwhile all the advice has been.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22830719
I've been down this road, so that helps.
0
 
LVL 3

Author Closing Comment

by:IUFITS
ID: 31510967
Thanks to all who gave advice, especially BrandonGalderisi.  The recommendations have put me on a good path to follow.  Again, I appreciate the responses greatly.  
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now