Need Help With An Sql Query

[Koobi]

Hi,
My SQL skills are pretty weak so I was hoping someone could give me a hand here for a digital library web app that I'm creating.


The current query:
[code]
    $_SYSTEM['query']['PENDING_BOOKS']     = "    SELECT `BOOKS`.`title`, `BOOKS`.`author_1`, `BOOKS`.`author_2`, `BOOKS`.`author_3`, `BOOKS`.`author_4`, `BOOKS`.`author_5`, `BOOKS`.`pages`, `REQUESTS`.`desired_prototype`, `TRANSACTIONS`.`cust_id`, `REQUESTS`.`delivery_date`, `REQUESTS`.`prototype_produced`, `REQUESTS`.`trans_id`, `MEDIUMS`.`title` as med_title, `CUSTOMERS`.`name`
                                    FROM `REQUESTS`, `BOOKS`, `TRANSACTIONS`, `MEDIUMS`, `CUSTOMERS`
                                    WHERE `TRANSACTIONS`.`trans_id` = `REQUESTS`.`trans_id`
                                        AND `TRANSACTIONS`.`book_id` = `BOOKS`.`book_id`
                                        AND `REQUESTS`.`production_status` = 'PEN'
                                        AND `MEDIUMS`.`type` = 1
                                        AND `MEDIUMS`.`medium_id` = `REQUESTS`.`desired_prototype`
                                        AND `CUSTOMERS`.`user_id` = `TRANSACTIONS`.`cust_id`;";
[/code]


As you can see, I select from 5 tables `REQUESTS`, `BOOKS`, `TRANSACTIONS`, `MEDIUMS` and `CUSTOMERS`
The thing is, the some of those selected fields hold foreign keys to other tables which hold the actual data.


I want my query to return `AUTHORS`.`title` for each of the `BOOKS`.`author_*` fields WHERE `BOOKS`.`author_*` == `AUTHORS`.`author_id` insead of just the foreign key.

Could somebody help me add this functionality to this query? I could always do it via PHP but I'd rather gather all this data in one SQL query.

here's an example of the output of my current query:
[code]
Array
(
    [title] => The Da Vinci Code
    [author_1] => 11
    [author_2] => 10
    [author_3] => 12
    [author_4] => 13
    [author_5] => 10
    [pages] => 56
    [desired_prototype] => 1
    [cust_id] => 10
    [delivery_date] => 0000-00-00
    [prototype_produced] => 0000-00-00
    [trans_id] => 1
    [med_title] => CD WAV
    [name] => Housni
)
[/code]



Thanks for your time.

[Wildbug]

I need some clarification(s) first.

1. Are the author_* fields primary author, secondary author, etc., for a particular book?
2. Are you expecting to return a list for each author_*?
3. Could you post the SQL for your database structure?

[Koobi]

[!--quoteo(post=388526:date=Jun 27 2006, 08:52 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 27 2006, 08:52 PM) [snapback]388526[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I need some clarification(s) first.

1. Are the author_* fields primary author, secondary author, etc., for a particular book?
2. Are you expecting to return a list for each author_*?
3. Could you post the SQL for your database structure?
[/quote]




1. yes, they are
2. each author_* field will hold an integer in the BOOKS field since it's a foreign key but `AUTHORS`.`title` would hold a string value (one authors name).
the `BOOKS`.`author_*` would point to `AUTHORS`.`author_id`
3. it's quite long so i decided to pastebin it: [a href=\"http://pastebin.housni.org/?show=6\" target=\"_blank\"]http://pastebin.housni.org/?show=6[/a]

thanks so far :)

[Wildbug]

I'm not sure you can do that in one shot, but let me think about it tonight.

[Koobi]

Thanks.

Any progress on this yet? Is this possible?

[Wildbug]

I can't come up with a way to do this.... sorry.

[fenway]

Maybe I missed the point... you simply want to add another FK lookup to get back info from the related author records?