SELECT answer FROM askmefi

November 29, 2005 4:04 PM

I need help with what I hope is a fairly simple mySQL query question.

I have a table with four columns: name1, name2, title, id. name1 is never null, but name2 is sometimes null, sometimes has a value that has been previously seen in name1, and sometimes has a unique value. Here's an example:

name1name2titleidSue Smith story title 11Joe Blow story title 22John DoeSue Smithstory title 33Jane RoeRichard Roestory title 44

How do I construct a query so that the output looks like this:

======================
Sue Smith
- story title 1
- story title 3

Joe Blow
- story title 2

John Doe
- story title 3

Jane Roe
- story title 4

Richard Roe
- story title 4
======================

...so that name1 and name 2 are kinda combined as the field by which you sort the data?

posted by Asparagirl to Computers & Internet (18 answers total)

Response by poster: Crap, my example data table worked fine on preview, but got stripped out on publish. Let me redo that...
posted by Asparagirl at 4:06 PM on November 29, 2005

Response by poster: Okay, I hope this one works:

name1          name2          title           id
------------------------------------------------
Sue Smith                     story title 1   1
Joe Blow                      story title 2   2
John Doe       Sue Smith      story title 3   3
Jane Roe       Richard Roe    story title 4   4

posted by Asparagirl at 4:08 PM on November 29, 2005

You mean "order by name1,name2" ? What've you tried?
posted by SpecialK at 4:12 PM on November 29, 2005

Response by poster: I've tried this:

Select
stories.name1,
stories.name2,
stories.title,
stories.id
From
stories
Order By
stories.name1 Asc,
stories.name2 Asc

But what it does, unless I'm screwing something up (which is quite possible) is first do a sort on the data by name1's column, then do a sort on the resulting data by name2's column, should there be anything in name2. What I want is the two columns to be combined, sort of, as in the example above, so that there is ultimately one list of names, which is then sorted by.
posted by Asparagirl at 4:20 PM on November 29, 2005

Do they have COALESCE in MySQL?
posted by matildaben at 4:26 PM on November 29, 2005

Well...one way to do this is create a new table where those columns are "merged" into one and then use a 'group by':

1. creat table X (name, title, id)
2. insert into X (select name1, title, id from stories)
3. insert into X (select name2, title, id from stories)

Then just do a:

select name, title, id from X
group by name;
posted by vacapinta at 4:27 PM on November 29, 2005

Best answer: select name1 as name, title from table
union all
select name2 as name, title from table where name2 is not null
order by name

yes I paid $5 to bring you this answer
posted by teki at 4:30 PM on November 29, 2005

If you are using a newish version of MySQL, I think you can use the 'concat' function (for concatenation).

It works like this,

select concat(name1, name2) as combined from table
order by combined

Hope this helps.
posted by Dag Maggot at 4:45 PM on November 29, 2005

I think this shows up a flaw in your table structure. Remove the author names from the table and move them to a second table named, say, authors. The table has two columns, one for storyid and one for the author name. For your example it would look like this:

storyid   name=====================
1         Sue Smith2         Joe Blow3         John Doe3         Sue Smith4         Jane Doe4         Richard Roe

Then you can attach as many authors as you like to each story, and do queries like you want to much more cleanly. eg

SELECT stories.name, authors.title FROM stories,authors WHERE storied.id=authors.storyid ORDER BY name,title
posted by cillit bang at 4:49 PM on November 29, 2005

Response by poster: teki, that's almost it! But it doesn't seem to be grokking the "if not null" part. That is, the query returns the same results with the "if not null" as without it. That leads me to believe that our blank table cells may not actually be null, but have a space in them or something...

Oh, and thank you for registering, and I hope you'll stick around. :-)
posted by Asparagirl at 4:54 PM on November 29, 2005

Best answer: yeah, sometimes you need to do IS NOT ' (double single quotes) rather than IS NOT NULL.

Don't ask me why, them's the rules, I just occasionally blunder into them.
posted by fishfucker at 4:58 PM on November 29, 2005

and YAY double single quotes gets stripped into one single quote

but you get the idea.
posted by fishfucker at 4:58 PM on November 29, 2005

Response by poster: "I think this shows up a flaw in your table structure."

I know, your way of table-building looks much better. Unfortunately, I have to work off a very-specifically-formatted Excel spreadsheet (maintained by other people to another team's standards) and need to periodically upload it into a mySQL database (overwriting the previous data every time), run a query, and then return the results as XML, which I'll then have to format.

(Don't ask.)

"I think you can use the 'concat' function"

Nope, it looks like that function smushes name1 and name2 together.
posted by Asparagirl at 5:01 PM on November 29, 2005

cillit bang, if we're going to normalize this (which apparently we're not), there should be three tables: one for stories, one for authors and one mapping the two together.
posted by yerfatma at 5:07 PM on November 29, 2005

what you're asking for as a result isn't a table.

do you want:

Sue Smith, story title 1
Sue Smith, story title 3
Joe Blow, story title 2
John Doe, story title 3
Jane Roe, story title 4
Richard Roe, story title 4
posted by andrew cooke at 5:08 PM on November 29, 2005

if so, what's wrong with teki's answer? you say it doesn't work with nulls - are you *sure* name2 is the "empty" cells?
posted by andrew cooke at 5:10 PM on November 29, 2005

oh, duh. should read more carefully. sorry. so you have it working now? i should go to bed...
posted by andrew cooke at 5:12 PM on November 29, 2005

Response by poster: Okay, I've got my magic bullet answer. Now I just need to make sure that the "empty" cells in my excel spreadhseet are actually either empty or null, and we'll be great.

Thanks, everyone!
posted by Asparagirl at 5:18 PM on November 29, 2005