asked on

Changing the join order

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).

Kevin Cross

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.

IUFITS

ASKER

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

IUFITS

ASKER

Err I meant, is there a way to force SQL Server to re-order the joins appropriately since this is all being generated dynamically?

Kevin Cross

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!

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IUFITS

ASKER

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

IUFITS

ASKER

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

Kevin Cross

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.

IUFITS

ASKER

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

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Think it needs to happen on the frontend is the point and just send correct SQL to database.

BrandonGalderisi

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.

IUFITS

ASKER

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.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IUFITS

ASKER

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:

Get the base table
Gather the tables a user has selected criteria from
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.

BrandonGalderisi

I've been down this road, so that helps.

IUFITS

ASKER

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.