Jump to content


Photo

writing smart multi-table queries


  • Please log in to reply
1 reply to this topic

#1 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 30 October 2006 - 08:26 PM

i'm building a simple website CMS.  on loading a page the database is queried for the page details. the query hits 3 tables..."pages","images" and "links". Can i write a single query to grab all the needed data from all 3 tables to build the page?  The "images" table contains one or more images for each page, and teh "links" table contains one or more links for each page. The tables look like so...

pages
-------------
id
page_title

page_pictures
-------------
id
picture_id
page_id

links
-------------
id
page_id
link

I'm using MySQL 4.1.15. So far i've only been able to get all this data using 3 separate queries.  Not sure if that's considered "messy" or if it's really the only way to go?

#2 fenway

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

Posted 31 October 2006 - 07:26 PM

Well, if you need all the pictures & links for a given page, you'd need two statements right there (I consider UNION still 2 statements, but you could get away with a single  UNION statement).  You'd have to JOIN in the pages table to be sure, but that's still not any less "messy", as you put it.
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