SQLTeam.com | Weblogs | Forums

Use a View or a Table


#1

I am trying to pull data from three different tables, two of which have a one-to-many relationship. The single record I need has most of the data. One table has dates and I need the oldest. The other has a sort order. Being new, this is very complicated. Can I just create a table, insert the data from the single record table, add new fields from the other tables, and use the new table as my data source? Is this a good practice?


#2

In general, creating a table such as you are describing would not be a good practice. That is because the new table will be out of sync when you insert/update/delete data in any of the three original tables.

You could create a view that would return the results you want - which would reflect the changes in the 3 tables correctly.

Views have some restrictions. It has to be a single query. You cannot define variables etc. in a view. If your query requires anything like that, your option would be to create a stored procedure that returns the data that you need.