1

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?

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.