Representing Marriages (of ppl that is!)

Results 1 to 2 of 2

Thread: Representing Marriages (of ppl that is!)

  1. #1
    Join Date
    Dec 1969

    Default Representing Marriages (of ppl that is!)

    Hi,<BR><BR>I am writing a contact address book of sorts and want to display a list of people in the db married and unmarried. I&#039;m trying to pull this all out in one query...<BR><BR>DB design is as follows... <BR><BR>Members table has id, name <BR><BR>And then a married table which has an auto inc id, and another 2 fields, maleid and femaleid which contain a member id..<BR><BR>How can I write a query to pull out both married and singles - so far I can get married, singles + married women..<BR><BR>Here is the query I have..<BR><BR>SELECT,<BR>FROM members AS t1, members AS t2<BR>RIGHT JOIN married AS m1 ON = m1.maleid<BR>RIGHT JOIN married AS m2 ON = m2.femaleid<BR>WHERE = <BR><BR>I&#039;m using mySQL<BR><BR>Regards<BR><BR>James

  2. #2
    Join Date
    Dec 1969

    Default Why the RIGHT JOIN???

    If you want *only* the married couples, then just an INNER JOIN is fine.<BR><BR>Or, as you started to use, an IMPLICIT inner join:<BR><BR>SELECT,<BR>FROM members AS t1, members AS t2, married<BR>WHERE married.maleid = <BR> AND married.femaleid =<BR><BR>Oh, wait...I *did* say you wanted both married and singles...<BR><BR>Hmmm...Well, I still don&#039;t get the RIGHT JOINs. Because the "members" table usages are on the left side, that says find all records in the "married" table regardless of whether there is a record in the "members" table. That&#039;s backassward, isn&#039;t it? You want *ALL* the members from the members table, no matter what. Hmmm...okay, I guess that&#039;s what you want for the women, as you&#039;ve constructed that.<BR><BR>Ugh.<BR><BR>Quite frankly, I don&#039;t see a way to do this without a UNION query.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts