I have an application(timematters) where the DB is created from tha app. when i check into DB i dont see any realtions between tables except sysid column which is in almost all the tables. Each sysid is different, when I create a view from EM it does inner join between the tables where the join is on sysid.because of that when i do a select view i dont get any rows returned. can i create a view on such DB's or else is there any way where I can get a report from different tables.
You're creating views through Enterprise Manager, that's why you get the joins preconfigured. There probably is a setting to suppress the automatic joining - I know there is one in MS Access, but I can't find it in EM. Your best option to use Query Analyzer - this also means you don't get those annoying keywords that you don't need, like LEFT OUTER JOIN, that EM insists on.
I tried running same type of script from QA but still thereare no records, i guess the column am joining has unique values, sysid in each table has different values.
Yes - looks like sysid is of no use for your queries. This might even be one of those overnormalised databases, where you don't have proper tables - just loads of metadata to sort out before you can assemble the actual data.
yeah u r right..this DB is something created from the Time Matters application by default. i dont think it as a perfect DB. But for me htere is no other go, i need a report where the data comes from such different tables. Is there a way to do?
EM was just joining on columns named sysid because it is designed to look for columns named ...id first, and columns with the same name second. So start looking for other column names that are shared between the different tables.
