List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 4 1999 5:15pm
Subject:Re: newbie SELECT question: please tell me this can be done!
View as plain text  
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)
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