Jump to content


Photo

Need Help With An Sql Query


  • Please log in to reply
6 replies to this topic

#1 Koobi

Koobi
  • Staff Alumni
  • Advanced Member
  • 419 posts
  • LocationColombo, Sri Lanka | South Asia

Posted 27 June 2006 - 01:50 PM

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:
    $_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`;";


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:
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
)



Thanks for your time.

#2 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 27 June 2006 - 02:52 PM

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?
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#3 Koobi

Koobi
  • Staff Alumni
  • Advanced Member
  • 419 posts
  • LocationColombo, Sri Lanka | South Asia

Posted 27 June 2006 - 03:07 PM

[!--quoteo(post=388526:date=Jun 27 2006, 08:52 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 27 2006, 08:52 PM) View Post[/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 :)

#4 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 27 June 2006 - 07:12 PM


I'm not sure you can do that in one shot, but let me think about it tonight.
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#5 Koobi

Koobi
  • Staff Alumni
  • Advanced Member
  • 419 posts
  • LocationColombo, Sri Lanka | South Asia

Posted 29 June 2006 - 01:27 PM

Thanks.

Any progress on this yet? Is this possible?

#6 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 29 June 2006 - 05:42 PM

I can't come up with a way to do this.... sorry.
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 July 2006 - 05:04 AM

Maybe I missed the point... you simply want to add another FK lookup to get back info from the related author records?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users