www.webdeveloper.com
Results 1 to 2 of 2

Thread: MySQL INNER JOIN confusion -- go easy on me, I'm learning

  1. #1
    Join Date
    Apr 2011
    Location
    Seattle, WA
    Posts
    265

    Exclamation MySQL INNER JOIN confusion -- go easy on me, I'm learning

    Tables include:
    plat: plat_title, plat_initial, builder_id
    company: company_title, company_type
    address: ref_city, ref_state, ref_map_x, ref_map_y
    graphic: img_file, img_type, featured, show_public, current_item, img_sequence
    img_rep: assoc_table, assoc_id, photo_id
    (among other fields)


    So what I need is to get the information (I was trying INNER JOIN but it doesn't like me) starting with the "plat" (community)
    • and then the name of the builder for that plat (plat.builder_id to company.company_title if company.company_type is 'builder')
    • and then the logo for the plat (via the "graphic" table using the "img_rep" table's assignment, i.e. img_rep.assoc_table = plat and img_rep.assoc_id = plat.id and img_rep.photo will be the graphic.id for several images -- the photo I need will have graphic.img_type = 'logo'
    • and then the "main photo" for the image, which will be the same as the logo except graphic.img_type = 'photo' and then it will need to have the lowest value of graphic.img_sequence value of the possible options



    I don't know if I should just do this with several queries or if there's a way to just grab what I need in one fell swoop... I'm trying to practice writing more efficient code; I used to just do each table in its own query, so I've been using MySQL a long time, but I haven't been doing it all that well, haha.

    Any advice or suggestions would be greatly appreciated; thanks in advance!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,361
    Quote Originally Posted by amandaNHT View Post
    Tables include:
    plat: plat_title, plat_initial, builder_id
    company: company_title, company_type
    address: ref_city, ref_state, ref_map_x, ref_map_y
    graphic: img_file, img_type, featured, show_public, current_item, img_sequence
    img_rep: assoc_table, assoc_id, photo_id
    (among other fields)
    ...
    It's not apparent to me what field relates one table to another. You cannot join two tables unless there is some common field (or fields) they share that can be used in the ON clause of a JOIN. For instance, how do you know which address record belongs with which company record? Same for which graphic goes with which company, and so forth.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles