0

I'm not seeing a clean way to write this query without subselects which I avoid because they are generally not portable, and harder to read and debug than individual queries.

Table A has exactly 2 foreign keys to table B, which are always different, but always defined. Sort of like:

MARRIAGE_TABLE
M_KEY
LAST_NAME
PERSON_HUSBAND_FK
PERSON_WIFE_FK

PERSON_TABLE
PERSON_KEY
SEX 
FIRST_NAME

The PERSON_HUSBAND_FK will always point at a SEX=MALE, and the WIFE_FK will always point at a female. There will always be one of each. (this is in no way a statement on same-sex marriage BTW I'm all for it)..

I want to create a result like:

MARRIAGE   HUSBAND     WIFE
--------   -------     ----
SMITH        TOM        KATHY
JONES        BILL       EVE

My current approach is to get all records from the MARRIAGE TABLE and store them in a hash. Then I augment the hash with names {wife_name} and {husband_name} using 2 more queries using the husband and wife FK's. Then I format and print the hash. It works, but I'm not wild about 3 queries per row.

I'm not sure I ever encountered a table having >1 FK to another table. I've done years of table-design, but I'm not really sure this design even meets normalization. It seems like no, to me. Like they created a many-many without an intermediate table; a cheat?

philipxy
  • 14,867
  • 6
  • 39
  • 83
user3250422
  • 139
  • 1
  • 1
  • 5
  • 1
    This seems very 20th century – Strawberry Jul 17 '17 at 20:57
  • Querying does not depend on FKs or other constraints. Eg if people can be of both sexes, so person_key isn't UNIQUE so no FK, or people can have no name, hence absent from Person--"person *person_key* has sex *sex* & first name *first_name*"--then meanings of rows of your tables (base & query result) would still be the same, eg query result "marriage *last_name* is husband *person_husband_fk* married to wife *person_wife_fk*". Also normalization does not depend on FKs. (It can generate FK cycles between components.) https://stackoverflow.com/a/33952141/3404097 – philipxy Jul 17 '17 at 21:11
  • PS 1. A FK just says values somewhere must appear somewhere else. 2. What do you mean when you say "meets [normalization](https://stackoverflow.com/a/40640962/3404097)"? Do you mean something other than "follows principles of good design"? – philipxy Jul 18 '17 at 03:26
  • No - I mean like 4th-normal: No table may contain 2 or more 1:n or n:n relationships that are not directly related. – user3250422 Jul 18 '17 at 19:29
  • Ok, although that description is so fuzzy it is not useful. (Marriage happens to be in 5NF.) But FKs have nothing to do with normalization & NFs. You seem to be confusing "relationship" in its sense of association/table/relation with its sense of FK. PS Use *@user* to notify *user* when others have also commented. – philipxy Jul 19 '17 at 05:59

1 Answers1

2

Just join table PERSON_TABLE twice:

SELECT m.last_name AS marriage, p1.first_name AS husband, p2.first_name AS wife 
FROM marriage_table m
INNER JOIN person_table p1 ON p1.person_key = m.person_husband_fk
INNER JOIN person_table p2 ON p2.person_key = m.person_wife_fk
Nedret Recep
  • 726
  • 5
  • 8