SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: Triple Join

  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Triple Join

    Forgive my ignorance on the matter, but I need help formulating a join query in MySQL.

    I have three tables:

    1. stylesheets
      • id: int, auto_increment, primary
      • name: varchar
    2. classes
      • id: int, auto_increment, primary
      • stylesheet: int
      • name: varchar
    3. styles
      • id: int, auto_increment, primary
      • class: int
      • attribute: varchar
      • value: varchar


    I'm having trouble describing in words what the query needs to retrieve... essentially, I need to produce all the necessary data for a stylesheet—the attributes and values of each class of the specified stylesheet.

    Any suggestions?

    Thanks in advance.

  2. #2
    SitePoint Enthusiast Redprince's Avatar
    Join Date
    May 2004
    Location
    Salzgitter, Lower Saxony, Germany
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, think i understand what you want

    Code:
    SELECT sh.name, c.name, s.attribute, s.value FROM stylesheets as sh, classes as c, styles as s WHERE sh.id = 'id_of_your_choice ;)' AND c.stylesheet = sh.id AND s.class = c.stylesheet
    Well, i´m not sure if you get what you want, but that´s the way i interpret your tables

  3. #3
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like this, maybe?
    Code:
    SELECT * 
       FROM stylesheets
    INNER
       JOIN classes
          ON stylesheet.id = classes.stylesheet
    INNER
       JOIN styles
          ON classes.id = styles.class
    WHERE stylesheet = 3
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to you both for your efforts.

    Redprince, yours worked perfectly. Thanks a million.

    Sam—thanks, but it returned a MySQL error.

    Again, thank you.

  5. #5
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's a shame because I did it the right way (I think!) but made a bunch of mistakes. And that's why you should always pay attention when writing queries!

    Code:
    SELECT ss.name, c.name, s.attribute, s.value
       FROM stylesheets as ss
    INNER
       JOIN classes as c
          ON ss.id = c.stylesheet
    INNER
       JOIN styles as s
          ON c.id = s.class
    WHERE ss.id = [WHATEVER]
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but I think I'll stick to Redprince's.

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are throwing away your life.

    I blame myself.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  9. #9
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's really hot in Atlanta. Really hot.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Apr 2003
    Posts
    4,095
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I ended up using yours anyway, so thanks.

  11. #11
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha! Glad to hear mine eventually worked, I was having my doubts.

    r937 would say something like:
    Spelling out the joins rather than sticking them in where clauses is ideal because it is more standard SQL and because it allows you to clearly separate the joins from the where limitations.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yep, i woulda said that

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


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
  •