Mysql – Determining Homogeneity by Common Foreign Keys

foreign keyMySQL

I manage a MySQL database of ten or so tables.

In higher code, I have a list of primary keys belonging to entries of some table A. These A entries have foreign keys to entries in another table B. The relationship is 1-to-many, so several As may point to the same B.

Problem: I need to determine if the list of primary keys I hold in higher code (Scala) is homogeneous. That is, if their foreign keys to B all correspond to the same entry in B.

Question: Would it be possible to determine this by analysing the results of a single SQL call?

Best Answer

For the table A (with B_id) and table B (with id)

Count of Primary Keys By Foreign Key

SELECT FK,COUNT(1) PKCount FROM
(SELECT IFNULL(B.id,0) FK
FROM A LEFT JOIN B ON A.B_id = B.id) K
GROUP BY FK;

If FK is 0, then PKCOUNT is a count of Primary Keys that do not have a Foreign Key

To be 100% homogenous, FK should never show up as zero(0) (In a Perfect World).

Count of Foreign Keys Used By Primary Keys

SELECT B.id,IFNULL(FK_Count,0) FKCount
FROM B LEFT JOIN 
(SELECT B_id id,COUNT(1) FK_Count FROM A GROUP BY B_id) K
USING (id) GROUP BY B.id;

If FKCount is 0, then B.id is a Foreign Key Not Used By Any Primary Key

To be 100% homogenous, FKCount should never show up as zero(0) (In a Perfect World).

Related Question