List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 4 1999 7:20pm
Subject:Re: newbie SELECT question: please tell me this can be done!
View as plain text  
Nathaniel Hekman wrote:
> 
> Great, Sasha, if there is a better way to do this, I'm ready for it.  I'm a
> lazy guy too.
> 
> Basically what I want is the output that I mentioned, where each person is
> listed once in the output, except if they have a maiden name then they're
> listed twice, with one entry saying "see their new (married) name here".
> 
> Here are some options I've thought of but don't really like:
> 
> 1) I could do this by including two rows for people with maiden names, like
> this:
> 
> +----+-------+--------+--------+-----------------------+
> | id | first | last   | new_id | email                 |
> +----+-------+--------+--------+-----------------------+
> |  1 | Nate  | Hekman | NULL   | NHekman@stripped |
> |  2 | Carol | Hekman | NULL   | carol@stripped      |
> |  3 | Carol | Brown  |      2 | NULL                  |
> |  4 | John  | Doe    | NULL   | john@stripped          |
> |  5 | Jane  | Doe    | NULL   | jane@stripped          |
> |  6 | Jane  | Doe    |      5 | NULL                  |
> +----+-------+--------+--------+-----------------------+
> 
> but suddenly some people have one id and others have two, which gets messy.
> So I think I'd rather stick to one row per person, or at least one id
> (though I'm willing to hear other opinions).
> 
> 2) I could do it all in my script rather than with the select.  When I get a
> recordset with a non-null maiden name, then I just print it out twice.  The
> problem with this approach is that I have to loop through the entire
> database, create my own big array (duplicating rows using this logic as
> necessary), then sort it myself before spitting any of it out to the screen.
> I'd rather have the DBMS give it to me all nicely sorted.
> 
> Those are really the only two options I see so far, but I'm sure I'm missing
> something simple.  This isn't a complex or unusual problem, is it?  That'd
> be just my luck:  decide to learn SQL because I think it'll make my life
> easier, then throw an impossible problem at it.
> 
> Thanks again for any help or nudging any of you can give.
> 
> Nate

Nate:

Doing it in the script would probably be most efficient
if you have to write the script that iterates through
every row of the result anyway. You will not need to
store anything in the array, just check for each
retrieved row if there is a maiden name, and print out
two version of the name if this is the case. The memory
requited will be no more than what it takes to store one
row of the results. The performance will be almost at
the speed of iterating through all the rows of the table
and printing out each.

Now if you are doing something like SELECT INTO OUTFILE
to export the data, that's a different story. In that
case you could still write a script to emulate SELECT
INTO OUTFILE instead, but it could be a lot slower than
writing a smart query. On the other hand, the "smart
query" because of it complexity might end up running
quite slow, and the script may catch up and beat it even
here.





-- 
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