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)
