How to get reference information between parent and child tables (MS SQL Server/MSAccess)? [modified]
-
Is it possible to get information about whether a child table has reference to a parent table through a certain record? For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together. If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
-
Is it possible to get information about whether a child table has reference to a parent table through a certain record? For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together. If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
It's a very simple query to return how many records in a child table match a parent's ID, however, you haven't really made it clear what you want to achieve. How many child tables do you have? Why don't you want to query them individually?
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
It's a very simple query to return how many records in a child table match a parent's ID, however, you haven't really made it clear what you want to achieve. How many child tables do you have? Why don't you want to query them individually?
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
Well as I said, if possible, I want to get a tabular output showing which tables are related to a certain primary table and the related data in them all-together. Please refer to the second paragraph in my OP. Feel free to ask if I'm still not clear.
-
Is it possible to get information about whether a child table has reference to a parent table through a certain record? For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together. If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
Have a read through this article on sp_help[^] There is a section in the article that shows the tables a given table references.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
Is it possible to get information about whether a child table has reference to a parent table through a certain record? For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together. If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
While there are system views which will identify the FKs between tables this is not a system issues but a data problem. You will need to craft a query that get the data you want the way you want it. There is no 'system' query/function etc that will service this requirement. It is a fairly simple excercise using either sub queries or left joins depending on the required output.
Never underestimate the power of human stupidity RAH