List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 4 1999 8:22pm
Subject:Re: newbie SELECT question: please tell me this can be done!
View as plain text  
Sasha Pachev wrote:
> 
> Nathaniel Hekman wrote:
> >
> > I'm new to SQL in general and of course to MySQL as well.  I've spent
> > several days of reading books and looking things up on the web, and here's
> > one problem that has stumped me so far.  I think I can figure out how to do
> > it with a SELECT inside a SELECT, or with a UNION, but MySQL doesn't support
> > those yet.  If anyone has advice, please share it!
> >
> > I have a table of people and their email addresses, like this:
> >
> > +----+-------+--------+--------+-----------------------+
> > | id | first | last   | maiden | email                 |
> > +----+-------+--------+--------+-----------------------+
> > |  1 | Nate  | Hekman | NULL   | NHekman@stripped |
> > |  2 | Carol | Hekman | Brown  | carol@stripped      |
> > |  3 | John  | Doe    | NULL   | john@stripped          |
> > |  4 | Jane  | Doe    | Smith  | jane@stripped          |
> > +----+-------+--------+--------+-----------------------+
> >
> > I want to do a SELECT that will give a table where those with maiden names
> > are listed twice, once with their maiden name moved to the last name
> > position, and a "see" column added (meaning "see so-and-so"), like this:
> >
> > +----+-------+--------+--------+-----------------------+------+
> > | id | first | last   | maiden | email                 | see  |
> > +----+-------+--------+--------+-----------------------+------+
> > |  1 | Nate  | Hekman | NULL   | NHekman@stripped | NULL |
> > |  2 | Carol | Hekman | Brown  | carol@stripped      | NULL |
> > |  2 | Carol | Brown  | NULL   | carol@stripped      |    2 |
> > |  3 | John  | Doe    | NULL   | john@stripped          | NULL |
> > |  4 | Jane  | Doe    | Smith  | jane@stripped          | NULL |
> > |  4 | Jane  | Smith  | NULL   | jane@stripped          |    4 |
> > +----+-------+--------+--------+-----------------------+------+
> >
> > The result doesn't have to be exactly like that, but it should:
> > 1) list people with non-null maiden name twice:  once as they appear in the
> > original table, and once with their maiden name moved to the last name
> > position.
> > 2) provide some way to programmatically identify the added rows so that they
> > can be flagged as really referring to a different row.
> >
> > In the end I want to write a script that will give an output something like
> > this:
> >
> > Name               Email
> > ----               -----
> > Brown, Carol       see Hekman, Carol
> > Doe, Jane          jane@stripped
> > Doe, John          john@stripped
> > Hekman, Nate       NHekman@stripped
> > Hekman, Carol      carol@stripped
> > Smith, Jane        see Doe, Jane
> >
> > I hope I've made myself clear enough.  I'll clarify more if necessary.  By
> > the way, I'm running MySQL V3.21.33b, but I could upgrade to the latest
> > version if necessary.
> >
> > Nate Hekman
> >
> 
> Nate:
> 
> While this can be done with a rather creative join ( I
> am too lazy to thing of one), my laziness prompts me to
> suggest a different solution. Well, this is not just
> laziness, I think there is a better way to accomplish
> the results you are shooting for in your application
> than to do this join. If you provide the info on the end
> result ( the stuff that has to go to the user), we may
> be able to think of a better solution.
> 
> In the mean time, Christian Mack, if he gets around to
> this message, will probably write a query that will do
> what you want. Writing queries seems to be his hobby :)
> 
> --
> Sasha Pachev

Hi Nate and Sasha

You can't do this with a JOIN.
Instead you would have to create a temporary table to finally select on it.

I would change the tabledefinition to use a 2 column PRIMARY KEY (id,nr).
id would be the same as before, and nr would be an additional column for numbering the
various aliases of a person.
The reason for doing so is, that a person can marry more than one time and therefore can
have multiple names not just two. 
(BTW: Not only women can have multiple names!)

The table would look like this:
+----+----+-------+--------+-----------------------+
| id | nr | first | last   | email                 |
+----+----+-------+--------+-----------------------+
|  1 |  1 | Nate  | Hekman | NHekman@stripped |
|  2 |  1 | Carol | Brown  | carol@stripped      |
|  2 |  2 | Carol | Hekman | carol@stripped      |
|  3 |  1 | John  | Doe    | john@stripped          |
|  4 |  1 | Jane  | Smith  | jane@stripped          |
|  4 |  2 | Jane  | Doe    | jane@stripped          |
+----+----+-------+--------+-----------------------+

Hope this helps
Christian

Thread
newbie SELECT question: please tell me this can be done!Nathaniel Hekman4 Jun
  • Re: newbie SELECT question: please tell me this can be done!Sasha Pachev4 Jun
  • Re: newbie SELECT question: please tell me this can be done!Christian Mack5 Jun
RE: newbie SELECT question: please tell me this can be done!Nathaniel Hekman4 Jun
  • Re: newbie SELECT question: please tell me this can be done!Sasha Pachev4 Jun
RE: newbie SELECT question: please tell me this can be done!Nathaniel Hekman4 Jun
  • Re: newbie SELECT question: please tell me this can be done!Sasha Pachev5 Jun
RE: newbie SELECT question: please tell me this can be done!Nathaniel Hekman5 Jun
RE: newbie SELECT question: please tell me this can be done!Nathaniel Hekman5 Jun