The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Easy SQL join question that has me stumped!

PHP + MySQL

Excuse the 101 question :-)

Lets say I have 2 tables, Person and Poems they've written:

Person (ID, Name)
======
1 Joe
2 Sam
3 Anne

Poem (ID, Person ID, Title)
====
1 1 Happy House
2 1 Sad Song
3 2 Tall Tail

So you can see that a join will give me:

1, Joe, Happy House
2, Joe, Sad Song
3, Sam, Tall Tail

But what I want is output like this:

1, Joe, Happy House, Sad Song
2, Sam, Tall Tail

Can this be done using SQL, or do I need to use the normal join and parse the result in PHP (probably looking for a change in ID to start a new line)????
Grown fat with decadence Send private email
Thursday, November 22, 2007
 
 
I don't believe it's possible unless you are using a cursor.
Drinking MGD
Thursday, November 22, 2007
 
 
I'm assuming that the reason you want to do this is that you've convinced yourself that it will simplify your display logic. If that is the case then you should just use a standard join. Your data format should generally not be dictated by the UI. When your UI changes in the future you will then need to change your SQL statement as well. If your SQL is in a stored procedure then you will need to make a database change just to support a slightly different UI layout. And if the UI ends up needing to support multiple views of the same data then you may find that you've painted yourself into a corner.

Keep it simple and use a standard join. The logic to display what you need is actually probably simpler than the complex SQL statement that would probably be needed to get the data into the desired format in the first place.

That's just my two cents. And if you have some other reason why you need the data in that special format then please ignore this post.
anon
Thursday, November 22, 2007
 
 
I agree with anon, completely...  but if you *really* want to do it then look at the GROUP_CONCAT MySQL function.
Almost H. Anonymous Send private email
Thursday, November 22, 2007
 
 
SQL is for retrieving data from a database mostly in table form. What you have there is a report. i.e data organised the way you want it. SQL was not designed with that in mind. There are probably some wierd and wonderful work arounds but generally you should keep your SQL straight so it is maintainable.

Grab the data from the table and then do with it what you will.
nessuno
Friday, November 23, 2007
 
 
Thanks guys, GROUP_CONCAT does what I need, but I can see that I need to consider separating DB logic from UI logic.
Grown fat with decadence Send private email
Friday, November 23, 2007
 
 
I hate these kinds of things...

I was once beaten up by a DBA/coder for doing a query-per-person.  He suggested to pull down the required info from both tables and do the grouping on the client.  If there aren't many people, you only hit the database once for this technique instead of once per person. 

SELECT
  p.name, p.id as person_id,
  po.id as poem_id, po.stuff...,
FROM person p
JOIN poems po ON po.person_id=p.id
WHERE po.something = ?
ORDER BY p.poem_name

You'll get something like:

person_id, name,poem, poem_id ...
----------
1,cory king, "I am", 1
1,cory king, "A poet",2
1,cory king, "And I didn't know it",3
2,joe blow, "but joe blow", 4
2,joe blow, "is not a poet", 5
2,joe blow, "yet he thinks he knows it", 6

Your application code would iterate over the list and group everything by person_id inside a for-loop.  I hate this technique because the code to keep track of person_id always looks ugly and brittle.  Ugly and brittle is usually because you optimized something and personally, if it is like 20 people, I'd go with query-per-person unless you can prove that it is slowing your database down.
Cory R. King Send private email
Sunday, November 25, 2007
 
 
>> I'd go with query-per-person unless you can prove that it is slowing your database down

Umm are you implying you first query all the people, then loop through those results and fire off a separate SQL query for each separate person?  If you do that, please email me your address, I have a team of DBA/Coders that want to break your fingers.

It may work fine with a handful of people, but it does not scale well at all.  Obviously firing off 1 query will ALWAYS be faster than firing off X queries.  I'm not BigO expert, but you've taken a system that performs near O(1)+1 and downgraded it to O(n)+1 just because you don't want to create an array!
TravisO Send private email
Tuesday, November 27, 2007
 
 
"If you do that, please email me your address, I have a team of DBA/Coders that want to break your fingers."

I'd be breaking my own fingers :-) 

In his instance, Query-per-person is quick to code and a bit less error prone than one-big-query.  But it will kill your database and maybe even cause lost fingers.  The good news is that it isn't hard to improve the query later on.

If performance or scalability isn't an issue *right now*, than I'd just stick a fat comment that says "yes, this sucks so lets do it right if it becomes an issue".

For all we know, this report only gets ran a couple times a day.  If it was part of something that was called a lot, for example the front page of a website, I'd think different.

That all said, I probably spent longer typing this than implementing either method :-)
Cory R. King Send private email
Tuesday, November 27, 2007
 
 
I don't know MySql, but here is an approach in Sql Server that doesn't use anything unusual:

select c1.author, max(c1.title), max(c2.title), max(c3.title)
from poems c1
left outer join poems c2 on c1.author = c2.author and c2.title < c1.title
left outer join poems c3 on c1.author = c3.author and c3.title < c2.title
group by c1.author
order by c1.author

You do have to know the number of columns before you write the SQL. It's very inefficient and creates a ton of rows that are discarded, so I don't recommend it.
Nutmeg programmer
Thursday, December 06, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz