Date: 03/12/06 (MySQL Communtiy)    Keywords: database

I'm working on a database to organize fan fiction stories. My current problem is figuring out how to generate a list of all the pairings that exist in the database for a given fandom. The relevant tables and columns are these:

table story: storyid
table fandom: storyid | showid
table pairing: storyid | pairid
table pairchar: pairid | charid
table person: charid | charname

Each story has one or more pairings. Each pairing has one or more (1-4 in practice) associated characters. Characters can belong to more than one pairing within a story, and multiple stories may have pairings that involve the same set of characters.

I need results like this:

CharA | CharB | CharC | CharD
Sara | NULL | NULL | NULL
Grissom | Sara | NULL | NULL
Sara | Catherine | NULL | NULL
Grissom | Sara | Catherine | NULL
Grissom | Catherine | Nick | Warrick

I know that getting the names in a consistent order is just a matter of comparing character ids and SELECT DISTINCT will condense the list. What I can't figure out is the correct syntax for what I believe should be a series of left joins to generate the list in the first place. Suggestions?

Source: http://community.livejournal.com/mysql/89003.html