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
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
