Jump to content

Archived

This topic is now archived and is closed to further replies.

Koobi

Need Help With An Sql Query

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[!--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 :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Thanks.

Any progress on this yet? Is this possible?

Share this post


Link to post
Share on other sites
Maybe I missed the point... you simply want to add another FK lookup to get back info from the related author records?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.