Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnsql
Posting Yak Master
161 Posts |
 Posted - 2009-02-24 : 09:23:48
|
Hi, I have tableA that has a primary column named "EmployeeID". Then there are 10 other tables that has a foreign key column named "EmployeeID" and the column references to the same column in tableA. I want to make a query likeselect *, HasDependents from tableA The extra column result in the query, HasDependents, gets a non-zero value if for some row in tableA, the primary column value of that row is used/referenced/stored in one or more rows in any of the other 10 tables. And the HasDependents gets value of zero if the primary column value of the row in tableA is NOT used/referenced/stored in ALL of the 10 other tables. Please tell me what is the most efficient way I can use to write the query? I do NOT want to use JOINs to get the result for HasDependents but system stored procedures or some better, quicker way.Thanks in advance.johnsql |
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2009-02-28 : 08:51:23
|
| Please give me some ideas or suggestions. Thank you. |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-02-28 : 10:28:30
|
| give your table structure(sample) that will help to give reply easy.========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-02-28 : 11:30:25
|
| John, the constraint that you do not want to join with the tables where the dependencies exist makes it a hard, if not impossible problem to solve - if that were not the case, this would be easy. That may be the reason you are not getting any responses. If there are any sysprocs or other SQL features that will allow you to do this I am not aware of those - and if there are, I suspect that under the covers they would be joining with the tables that have the foreign keys. Possible places to look are: how SQL engine implements referential integrity via foreign keys and how cascade deletes and updates are implemented. |
|
|
|
|
|
|
Find the dependents on a column of primary table