HomeFlySpeed SQL QueryAuto join to views

Auto join to views


Is there a way to automatically connect a table to a view (as occurs with a foreign key reference)?

My situation is that I have a single fact table 'sales'. Table sales has columns bill_to_customer_id and ship_to_customer_id. These ids both reference a single physical table 'entities'. Up until getting the FlySpeed tool, I have written out all of my SQL statements and submitted them from the command line. So I never bothered with views (or even foreign keys for that matter).

I would connect the customer ids to the single reference table as you would expect:

Select .... from sales inner join entities as entities1 on bill_to_customer_id = entities1.entity_id inner join entities as entities2 on ship_to_customer_id = entities2.entity_id
where ...

// End example

Now that I'm working with a visual tool, I find that I need either multiple tables or multiple views so that bill_to_customer_id and ship_to_customer_id can be constrained separately. I made the views. FlySpeed sees them. But since there are no foreign key relationships between sales and these views (and apparently there can't be because they are views), FlySpeed doesn't automatically setup a connection between sales.bill_to_customer_id and my bill-to customer view.

I can setup these connections manually, but I would prefer that it happen automatically when I drag the sales table and the relevant customer view onto the query builder window. It would be easier for me, and it would be much easier for the other users at my company.

So far, I have maintained my personal database for my own use. SQL-intensive use was ok becuase I have been studying SQL. But now that I'm trying to make this database available to others, I want to make things as simple as possible for them. (I also want to make them simple for myself, as I've gotten rather tired of writing a paragraph of SQL every time someone asks me for a report. If I can further make things easier for myself with auto-joins between particular views and particular tables, that would be great.)

I'm aware that I can drag the same customer table onto the query builder and give each copy of the table its own alias. But if I define a foreign key relationship for each customer id in 'sales' (in order to get the auto-joining action), then I get each id auto-joining to each copy of the customer table (i.e. two joins to each copy of the customer table).

Thank you for your time!
Matthew Pugsley | email
February 26 2009, 11:28 AM
Dear Matthew,

Thank you for your suggestions.

It is possible to implement these changes in the near future. We are plan to implement the metadata editor in Active Query Builder project in a few months. Of course, we'll implement this tool in our end-user solution as soon as it will be ready.
Sergey Smagin | email
5 days, 19 hours since original post
This topic is closed