# Determining Homogeneity by Common Foreign Keys

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 `A`s 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?

• I have found the equality operator is quite sufficient for testing equality of primitive objects. – Pieter Geerkens Jul 29 '14 at 1:03

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).

• Thanks Rolando. I think the latter is what I'm looking for. I'm having a bit of trouble with the syntax, but I'll study it more in depth tomorrow. – Colin Woodbury Jul 29 '14 at 4:28
• Perhaps not. I was looking only to check the Primary Keys that I have in some List at the Scala level, not the entirety of `A`. Forgive me if I'm misinterpreting your `SELECT`. – Colin Woodbury Jul 29 '14 at 4:33
• I've got it: by adding `AND A.id IN (...)` after the `ON A.B_id = B.id` in your first select, I get the result I need. The `(...)` are the ids from `A` that I hold at the Scala level. If there are two entries in the resulting table, the entries from `A` all have FKs to the same entry in B. If not, then they don't. – Colin Woodbury Jul 29 '14 at 17:29