List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 4 1999 8:14pm
Subject:Re: newbie SELECT question: please tell me this can be done!
View as plain text  
Nathaniel Hekman wrote:
> 
> No, you misunderstood one important aspect:  the end result must be sorted.
> So my script can't just spit out two lines for each person with a maiden
> name.  For example, Jane Smith whose maiden name is Brown, she would have to
> show up as "Brown, Jane:  see Smith, Jane" in the "B" section of the
> listing, and as "Smith, Jane" in the "S" section.
> 
> If the DBMS would sort the list before giving it to my script then that
> problem would be solved.  Plus it would be much easier if I wanted to sort
> on other fields (say I want to sort on first name, or on email address, or
> on some more data in the DB) if the only change were in the SQL rather than
> the script itself.
> 
> Am I right in thinking a UNION and/or nested selects would solve this
> problem quite simply?  Maybe I should look at a different DBMS that supports
> those, or hang tight until MySQL adds support for them.
> 
> Nate
> 
> -----Original Message-----
> From: Sasha Pachev [mailto:sasha@stripped]
> Sent: Friday, June 04, 1999 1:21 PM
> To: Nathaniel Hekman
> Cc: 'mysql@stripped'
> Subject: Re: newbie SELECT question: please tell me this can be done!
> 
> 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)


First lets redo the schema a little bit

create table person
 (
  id auto_increment primary key,
  fname char(30),   
  email char(30)
 );

create table maiden_or_last
 (
  id int not null,
  m_or_l_name char(30),
  name_type enum ('M', 'L')  
 );

Then the query becomes:

 SELECT fname, m1.m_or_l_name, m1.name_type,
m2.m_or_l_name as maiden_name, email FROM maiden_or_last
AS m1,
 maiden_or_last AS m2,
 person AS p
 WHERE m1.id = p.id AND m2.id = p.id AND m2.name_type =
'M' ORDER BY m_or_l_name;

I have not actually tested this, so it may not work
right "out of the box", but you get the idea.

I do not see a way to do it in one query without
temporary tables and without UNION with your current
table structure.

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