SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Stucture?? Need Help!

    I have the following tables that I'm trying to make work together:

    http://www.athomeinkaty.com/images/diagram.jpg

    In the communities table, the school district id is pulled from the school district table, and the each of the school id's are pulled from the schools table. What I'm needing is a way to pull the school district NAME based on that ID, as well as all the information about each of the schools based on the school id's.

    I'm sure I'm not making sense, but I need a way to pull all the school information (text not id's) based on the community ID. Can someone point me in the right direction on how to contruct my select statement? Or if you can think of a more efficient way to do this, please let me know!

    THANK YOU EVERYONE!!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this sounds way too much like homework

    i'm sorry, but it does, and we don't do people's assignments here

    we do coach, though, and i can give you a hint

    you'll need to join the communities table to the schools table three times, with table aliases:
    Code:
    select ...
      from communities as c
    inner
      join schools as e
        on e.sid = c.elem
    inner
      join schools as m
        on m.sid = c.middle
    inner
      join schools as h
        on h.sid = c.high
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm far too old to still be doing homework, but thanks for the compliments. No, a couple of my partners and I are wanting to build a 'new home website' for our community, where visitors can choose a community or a builder, or price range, etc... and all pertinent information about that community is displayed..

    if weren't for all the school info, this would be simple for me, but pulling multiple info from multiple tables is something i just haven't figured out yet.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bvarvel
    ... but pulling multiple info from multiple tables is something i just haven't figured out yet.
    i know how you feel -- it may not seem like it sometimes, but i do

    do you swim? have you ever tried to teach someone to swim? you tell them "it's easy" -- and it is, once you've learned

    but they're still pretty anxious about it until they realize that the secret to learning how to swim is: don't be anxious!

    SQL is like that too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    googlicious graymatter bvarvel's Avatar
    Join Date
    Sep 2002
    Location
    Katy, TX
    Posts
    956
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Funny you mention teaching to swim, I used to do that in another life. No - sql / php development is a side hobby that I love, and I don't have nearly the time to devote to it as I used to. I'm pulling 12 hours days 7 days a week with my real estate business, hence the reason I'm trying to put together a website to market to new home buyers.

    for the life of me though, I can't figure out what the sql statement you wrote above does. is it pulling all the info from the community table, or the schools table? i need to pull all the community info as well as all the school info and display it on an output page.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the sql fragment i gave above joins each row from the communities table to three different rows of the schools table

    you'll want the SELECT list to look something like this:
    Code:
    select c.cname
         , e.sname as eschool_name
         , m.sname as mschool_name
         , h.sname as hschool_name
         , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    London
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    the secret to learning…is: don't be anxious!

    SQL is like that too
    i've started to think that the secret to not being anxious is slight boredom or indifference combined with determination to set out clearly what you know already...AND you must ask yourself at least one question...unless you ask yourself a clear question every so often, you is stuck

    marmot


Bookmarks

Posting Permissions

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