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
