Combining rows using SQL

[johnnyb]

Ok, here's my dilemma.....

I am creating a database of ballets - but for an example here let's use something people are more familiar with: songs.

So, I have a table 'song', a table 'person' and a table 'profession'.

The people in 'person' have various jobs, which are referenced in 'profession'. This way a person can be a singer, songwriter, producer, etc. or any combination of the above.

I need to make a query that will get all of the information about a particular song - not so hard using a simple join - but the problem I am running into is when I have a song where 2 people collaborated on the writing of the song - so I now have 2 songwriting credits and my join doesn't work.

I know I'll have to put another table in to enable the many to many relationship between 'song' and 'person' - I have no problem with this. My problem is how to combine data from 2 different lines in the 'person' table into a single line in the result set of my query.

Does anybody know how I can do this? It is perfectly fine if the two people's names from 'person' are simply concat 'ed into one big text field... Although I would also be interested to know how to keep them seperate in the result set.

Thanks in advance for the help.

John

[raf]

As far as i know and understand your setup : you can't. Not in one query and i even doubt if you could do it with a temporarely table or so. But what is the problem if you have multiple record in the recordset? With a bit of extra PHP, you can output is as you like.
I don't know your db-design, but normally you should have 1 factstable that contains only the id's of the person, job and song table (dimension-tables).
So 1 record for each person/job/song combination. You can then run your select on this factstable and have a four table join to get the details for each person, song, job.

[johnnyb]

I was afraid of there being no way in SQL to do what I want. I'm trying to do it in SQL instead of PHP simply to minimize processing time - 1 call to the DB instead of 3 or 4.

I will be running the setup you are talking about with the factstable raf - I had just hoped that I could do the join to end up with everything I need to know about each song in 1 row in the resultset, however if there are 2 people involved in any 1 job I don't see how I can do that.

John

If anyone else comes up with a better way... let me know.

[raf]

There are sollutions of course, but you need to concider a few things:
- databases are made for running querys agains. One of the things mySQL got right are fast responsetimes for selects !
- the dimensiontables will be fairly small. I mean, we're probably not talking about thousands of records in each of these tables, right?
- the factstable is also probably less then 10k records, and only consists of numerical variables, that can be indexed.
- you're only reading from the tables
Bottomline : the responsetime will probably be neglectable. I've got pages running with 2k lines of code where 30-40 querys are made agains a relatively big db (tables up to 50k records) and i think the average processingtime (PHP and db-querys) is about 0,4 seconds, which i think is acceptable.
You can speed things up, but you'll loose on flexability + have more data redundancy.

A possible way to get them in one line, is select on a pre-generated table, that contains all needed data from the 3 dimensiontables, 1 line per song, and where you have a large number of columns (for instance 30 X 2 --> job1 + person1 ... job30 + person30.

You could then update this table if anything changes for the song/job/person combinations (one added, one deleted, one updated ...)+ if anything changes in one of the dimensiontables (person renamed/deleted, job renamed....)

And you need to do all this through PHP and at any point in your app where you change any of the related data.
You get the idea? You woul then be loosing all advantages of a relational db for this pre-generated table ...(or to gain 0,1 second processing time)

If mySQL would support triggers, then you could concider it, but managing all this all through PHP ...

I would recommend trying the 4 table join aproach first and clock your processingtimes, before starting to set up something as described above (cause you'll probably wount need it)

[johnnyb]

I think you're right there. My biggest goal in the DB is as little data redundancy as possible. The last incarnation of my website had WAY too much of it so i'm trying to get rid of it.

I have now made the factstable and am putting a couple of other things into my DB.... I think it'll work fine the way you describes, (the 1st way ;) ).

I knew mySQL was fast, but I wasn't sure just how fast, you've kind of shed some light on that, thank you.

John.

[raf]

I knew mySQL was fast, but I wasn't sure just how fast, you've kind of shed some light on that, thank you.

Make sure you create an index on each of the 3 'foreign key' variables in the factstable. Specially if the values for these variable occur frequenly, then this will have a serious performancy improvement.

Happy coding !

[Dylan Leblanc]

Try using GROUP_CONCAT(), http://www.mysql.com/doc/en/GROUP-BY-Functions.html

It will do what you are trying to accomplish. However, I think this function is only available with newer versions of MySQL.

[raf]

That would indeed solve it, but it's from 4.1 up ...