Search for relationship

Question

Post reply

Search for relationship

sdhamani1020

Old Hand

Points: 329
More actions
June 13, 2013 at 1:05 pm

#294756

I have a table having company names and another table having Revenueid and amount of Revenue but dont seem t get the relationship where it says which company pays what amount oof revenue.
How can I see that. No common columns in these two tables.
There are lot of other tables in the DB but I dont seem to find one which can show the relationship and get me the data.
Pls suugest.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Could you post DDL for both tables? Some comments on what the columns are could be useful.

Additionaly, you could use the system views to find any Primary keys or Foreign keys on both tables.

SELECT *

FROM sys.objects

WHERE type IN ('F', 'PK')

AND parent_object_id IN( OBJECT_ID( 'Saldos_Emp', 'U'), OBJECT_ID( 'AnotherTable', 'U'))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dave62 SSCertifiable Points: 7459 More actions · Answer 2

This will be pretty hard to answer definitively without having a lot more information about all the tables and fields in the database.

Totally grasping at straws, I'm wondering if the table that has the company names also has a CompanyId field and if so then maybe the CompanyId field is related the RevenueId field in the other table.

One possible way to see relationships is to add the table with the company names to a new database diagram in SSMS and then right-click in the new diagram window and pick "Add Table..." to add the table with the company names to the diagram. Once the table comes up in the new diagram window you can right-click the table and pick "Add Related Tables". That option will be greyed-out (disabled) if the table has no related tables. If the option is enabled and you click it, all the related tables will be added to the diagram. You can then check the properties of each relationship to see what columns in the other tables are related to what columns in the table with the company names.

sdhamani1020 Old Hand Points: 329 More actions · Answer 3

I tried this option. Apparently the table with CompanyID does not have any related table.

However , the table with revenue id , does bring up lot of related ones....

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

sdhamani1020 (6/13/2013)
I tried this option. Apparently the table with CompanyID does not have any related table.
However , the table with revenue id , does bring up lot of related ones....

Well since we can't see your screen we can't really do much to offer as help here. There must be something somewhere that ties these tables together. Maybe you have to go through another table or two to get there?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dave62 SSCertifiable Points: 7459 More actions · Answer 5

sdhamani1020 (6/13/2013)
I tried this option. Apparently the table with CompanyID does not have any related table.
However , the table with revenue id , does bring up lot of related ones....

Sometimes the developer who setup the original design was "nice" enough to put fields that are related in tables but without actually creating foreign keys. So it's possible that CompanyID is related to revenue id without a foreign key existing. Or CompanyID may be related (without a foreign key) to a field in one of the other tables that is connected to revenue id.

If you find that to be the case, you may want to consider putting the foreign key(s) in place to make your life and the next guys easier going forward. 😉

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

Do you have any database documentation?

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

OTF SSCertifiable Points: 6054 More actions · Answer 7

This is a difficult one to answer without access to your database or a lot more info. on it.

Maybe a database/entity relationship diagram.

Tables can be "related" without "hard coded" Foreign Key Relationships in place at which point it pretty much comes down to a bit of investigation on your part to detect the relationships.