Auto-joining tables

  • Thread starter Thread starter David Thielen
  • Start date Start date
D

David Thielen

Hi;

We are using the PK:FK metadata to automatically join tables. However,
we are hitting a problem. In some cases when combining 3 or more
tables we don't have a single direct path through all the tables. So
we end up joing table_1 to table_2, and then in joining table_3 we go
part way back down the joins between 1 & 2.

Is there a suggested best approach to how to take all the needed join
trails and build up a join between all of them?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi Dave,

Could you please elabrate the scenario? Based on my understanding, you are
creating an application that can parse the database' metadata and
illustrate them in your own UI, right? So the application acts like the
Visual Studio's Query and View Designer. But we encounter problem when the
relationship exists for 3 or more tables, right? If so, would you mind
sharing the DataTable's schema?

And if we add these three tables to Visual Studio Query and View Designer,
does the diagram generates OK? Any screenshot to show the problem
intuitively will be helpful.


Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team
 
Hi Dave,

Could you please elabrate the scenario? Based on my understanding, you are
creating an application that can parse the database' metadata and
illustrate them in your own UI, right? So the application acts like the
Visual Studio's Query and View Designer. But we encounter problem when the
relationship exists for 3 or more tables, right? If so, would you mind
sharing the DataTable's schema?

And if we add these three tables to Visual Studio Query and View Designer,
does the diagram generates OK? Any screenshot to show the problem
intuitively will be helpful.
Click to expand...

Lets say I have these relationships:

Table1 - Table2
Table2 - Table3
Table2 - Table4

In my select I am using Table1, Table3, & Table4. So I need to join:

Table1 -> Table2 -> Table3
and
Table1 -> Table2 -> Table4

What we are presently doing (because we just build it up in order of
use) - this works but is ugly & possibly inefficient:

Table1 -> Table2 -> Table3 -> Table2 -> Table4

I'm wondering if there is a better way to build up the inner joins in
the select.

Note - the relationships all show correctly. The question is how to
write the inner join across all of them.

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi,

I am sorry I still do not catch you very well, I think. Could you please
let me know if my understanding in the first reply is right? Do you mean
you are creating your own designer of SQL table relationships? And is the
graphic in Visual Studio or SQL Management Sutdio generated as expected? If
this is case, I think it is only related how we design the drawing
algorithm based on the retrieved schema. We need to write an algorithm that
can detect and avoid the duplicated table in multiple relationships.

If your question is only regarding how to do multiple inner joins, I think
we just write SQL commands like this,

Select [Orders].OrderID ,
[Order Details].Discount,
[Customers].CustomerID ,
[Shippers].ShipperID ,
[Shippers].CompanyName
from
[Orders] inner join [Order Details] on [Orders].OrderID = [Order
Details].OrderID
inner join [Customers] on [Orders].CustomerID = [Customers].CustomerID
inner join [Shippers] on [Orders].ShipVia = [Shippers].ShipperID

I use Northwind as the test database, here Order Details is Table1, Orders
is Table2, Customers is Table3, Shippers is Table4


Best regards,
Colbert Zhou
Microsoft Online Support Team
 
Hi,

I am sorry I still do not catch you very well, I think. Could you please
let me know if my understanding in the first reply is right? Do you mean
you are creating your own designer of SQL table relationships? And is the
graphic in Visual Studio or SQL Management Sutdio generated as expected? If
this is case, I think it is only related how we design the drawing
algorithm based on the retrieved schema. We need to write an algorithm that
can detect and avoid the duplicated table in multiple relationships.

If your question is only regarding how to do multiple inner joins, I think
we just write SQL commands like this,

Select [Orders].OrderID ,
[Order Details].Discount,
[Customers].CustomerID ,
[Shippers].ShipperID ,
[Shippers].CompanyName
from
[Orders] inner join [Order Details] on [Orders].OrderID = [Order
Details].OrderID
inner join [Customers] on [Orders].CustomerID = [Customers].CustomerID
inner join [Shippers] on [Orders].ShipVia = [Shippers].ShipperID

I use Northwind as the test database, here Order Details is Table1, Orders
is Table2, Customers is Table3, Shippers is Table4
Click to expand...

Yes that is what we do. I think what you did above is the answer I am
looking for:

[Customers] on [Orders].CustomerID = [Customers].CustomerID inner join
[Shippers] on [Orders].ShipVia = [Shippers].ShipperID

You join Customers to Shippers but the connection is Orders
(previously listed) to Shippers. That is what I was not thinking of.

Thank you - great job!!!


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Back
Top