February 15, 2011 at 9:50 am
When I need to create a ‘View’ from the columns of different tables in a Database,
Is it necessary that the tables involving in that view should be related directly or indirectly?
That is, should any 2 tables have a common column or an indirect Relationship?
Or is it still possible to create a view with independent tables without any kind of relationship between them (tables)?
Please clarify
February 15, 2011 at 9:54 am
rohaandba (2/15/2011)
When I need to create a ‘View’ from the columns of different tables in a Database,
Is it necessary that the tables involving in that view should be related directly or indirectly?
Not at all ; A CROSS JOIN or APPLY between two tables does not necessarily involve a WHERE / ON clause , so u can create a view from two tables without relationship between them. Also a UNION/UNION ALL of two tables is also possible!
That is, should any 2 tables have a common column or an indirect Relationship?
Not required.
Or is it still possible to create a view with independent tables without any kind of relationship between them (tables)?
Please clarify
Yes, pretty much u can!
February 15, 2011 at 9:55 am
No you can't. You need to create a form of relationship. Otherwise why have them in the same view?
EDIT: Maybe I misunderstood this!
February 15, 2011 at 10:10 am
I'm with Cold Coffee on this, there are mechanisms that don't involved a JOIN to put data from different tables together, but you're still defining some type of relationship between these tables, so you need to understand what those different functions can and can't do. When you say completely unrelated data, I have to ask, then why do you want it together in a view? It must be related in some fashion, else why the inclusion?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2011 at 4:41 pm
... and please remember that a view of two tables with no row level relationships WILL cause a CROSS JOIN where the number of rows in the view will be a product of the number of rows in the tables. In other words, if both tables only have 10,000 rows each, the view will have 100,000,000 rows in it and will eat the face off your server when you join to the view. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2011 at 8:06 am
Actually I will explain in detail.
I have a data base ‘x’.This database ‘x’ has 20 tables.
Among these 20 tables there is one ‘Root Table’ with a Primary key and there are 5 other tables with foreign keys referring to this ‘Root Table’ primary key.
There are 6 other tables which are independent. i.e. these 6 tables are not related to this ‘Root Table’ and the other 5 tables which are in reference with this ‘Root Table’.
These 6 tables neither have foreign keys referring to the ‘Root Table’ nor any common columns with Root Table and its 5 link tables.
Now I am suppose to create a View which involves the columns from the ‘Root Table’, its 5 link tables and the 6 independent tables.
Can any one suggest me how to approach this? Thank you
February 16, 2011 at 8:13 am
That sounds like a mess and I don't see what use a view containing that (even if possible) would do. What do you want to show? Where do you want to display it? What format do you expect it to appear in?
Stored Procedure sounds more your cup of tea with the first part based on the root and the 5 foreign key tables and then 6 select statements for the other tables therefore providing you an sp that will produce all of that information one after the other.
February 16, 2011 at 8:23 am
Well, no, not really. You're saying that the other tables have no type of relationship to the other data at all, but, you want to somehow put it all together.... Why?
You can simply list the tables side-by-side in a query, but as Jeff has explained, this will simple be a product of combining every value in every row with every value in every other row of all the tables. That can satisify certain requirements. Is that what you need? Doesn't sound like it to me.
You're working with data stored in referential storage engine, SQL Server. The way the data is stored and retreived involved relationships between the data. You don't have any, but you're trying to somehow create some out of the air. It just can't be done. I'd go back to those defining the requirements and try to drill down better on what they're looking for, because this makes no sense at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2011 at 12:06 pm
Jeff made the point I was going to... this has been referred to as a data explosion and I can think of no use for the results.
Are you sure that there's no relation at all? It seems to me that the other 6 tables MIGHT be some sort of lookup tables? In which case keys from them would be in one or more of your "related" tables and you would join on that key to retrieve the value of the lookup?
....otherwise, like someone else said, if they're truely not related what would be the point?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply