Find the dependents on a column of primary table

Question

Find the dependents on a column of primary table

johnsql-193053

SSCommitted

Points: 1657
More actions
February 24, 2009 at 7:26 am

#206918

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 like
select *, 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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there is no built in, easy way.

the procedure sp_fkeys [YourTableName] will report all the tables that have a FK relationship with your table, but there is no way to get the count of the rows on a PER key basis without using a join.

you'll HAVE to do something like this: there is no other way.

select tableA.*, SUM(MYALIAS..CNT) AS HasDependents

from tableA

LEFT OUTER JOIN

(

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE1 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE2 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE3 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE4 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE5 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE6 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE7 UNION ALL

SELECT FKID,COUNT(FKID) AS CNT FROM TABLE8

)MYALIAS ON tableA.PKID = MYALIAS.FKID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 2

You could build something using the sys.foreign_key_columns system view. Not sure why you wouldn't want to use Joins.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 3

Something like this would do it very, very efficiently. Too bad you don't like joins. (Refusing to use Joins in SQL is kind of like refusing to use a racket in tennis.)

select schemas.name, tables.name, columns.name,

case

when exists

(select *

from sys.foreign_key_columns

where parent_object_id = columns.object_id

and parent_column_id = columns.column_id) then 1

else 0

end as HasDependents

from sys.tables

inner join sys.columns

on tables.object_id = columns.object_id

inner join sys.schemas

on tables.schema_id = schemas.schema_id;

Could be extended by adding something like "and referenced_object_id != columns.object_id" to the sub-query if you don't want self-referent keys (joins within the same table). Could drop the Case part and change the sub-query to "select count(*)" and get the actual number of references.

All kinds of options with something like that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ScottPletcher SSC Guru Points: 101134 More actions · Answer 4

Please tell me what is the most efficient way I can use to write the query?

Use EXIST with OR between to check the other tables; check the most likely table first, the second-most likely second, etc., like so:

SELECT *,

(SELECT CASE WHEN

EXISTS(SELECT 1 FROM subtable5 WHERE subtable5.key = maintable.key) OR

EXISTS(SELECT 1 FROM subtable3 WHERE subtable3.key = maintable.key) OR

EXISTS(SELECT 1 FROM subtable1 WHERE subtable1.key = maintable.key) --OR

--same for remaining 7 subtables, in freq. order

THEN 1 ELSE 0 END) AS HasDependents

FROM maintable

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 5

GSquared (2/24/2009)
You could build something using the sys.foreign_key_columns system view. Not sure why you wouldn't want to use Joins.

Thanks for your reply.

The reason I do not want to use JOINs beause number of dependent tables that depend on tableA will be increased, and the same foreign column name in those dependent tables would reference or not reference to the primary column in tableA.