SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Perfect INNER JOIN query on multiple tables

    Hello forums!!

    I have some questionaire regarding INNER JOIN among multiple tables.
    I always got confused about where to start (which table) & then which table thereby in case of multiple tables.

    I have screenshot of ERD attached, I think relation between them is obvious as picture worths more than thousands of words. I want to perform the INNER JOIN query on this.
    I would like to know the which table to start with and then which table(moreover perfect inner join query) , I mean to say like this:
    Code MySQL:
    SELECT
    *
    FROM
    ?1
    INNER JOIN ?2 ON ...
    INNER JOIN ?3 ON ...
    ...so

    Can somebody make perfect INNER JOIN query on those table ( attached ERD)?
    Thanks
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it all depends on which data you want back

    are there any WHERE conditions?

    if not, then it doesn't much matter which tables you start with

    by the way, you might also want to take a moment and explain what your tables are for

    what's a param?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok let me explain about tables:
    shops (it's clear i think)
    widgets (it's clear too)
    shops_widgets (shop & widget are related here)
    widget_params (params of a widgets are related here)
    shop_widget_params (relates the param values of a widget)

    What i want to fetch is:
    widget_params.param & shop_widget_params.param_value

    And Where condition would be shop_id

    Hope this is much clearer now.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure if that would work for what you need it to do:

    Code SQL:
    SELECT
          widget_params.widget_param
        , shop_widget_params.param_value
    FROM
        widget_params
    INNER JOIN
        shop_widget_params
    ON
        shop_widget_params.widget_param.id=widget_param.id
    WHERE
        shop_widget_id=1

    Could you provide some sample data? My suspicion is that your tables could to with being reorganised and/or normalised.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all sorry for the late reply.
    If i write following query
    Code MySQL:
    SELECT
    	wp.widget_param,
    	swp.param_value
    FROM
    	shop_widget_params swp
    	INNER JOIN shops_widgets sw ON swp.shop_widget_id = sw.id
    	INNER JOIN widget_params wp ON swp.widget_param_id = wp.id
    	INNER JOIN widgets w ON wp.widget_id = w.id #*
    	INNER JOIN shops s ON sw.shop_id = s.id
    WHERE
    	s.id = ?

    is this the perfect one?

    or am i missing any other join conditions here? like
    Code MySQL:
    AND sw.widget_id = w.id
    in #* line

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT
        wp.widget_param,
        swp.param_value
    FROM
        shop_widget_params swp 
        INNER JOIN widgets w ON wp.widget_id = w.id #*
       INNER JOIN shops_widgets sw ON swp.shop_widget_id = sw.id 
        INNER JOIN shops s ON sw.shop_id = s.id
       INNER JOIN widget_params wp ON swp.widget_param_id = wp.id
    WHERE
        s.id = ?

    What about changing the orders like above

  7. #7
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are the users not getting my post meaning ?

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    Are the users not getting my post meaning ?
    Post a create table query and some sample data.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         w.id
         ,wp.widget_param
         ,swp.param_value
      FROM
         widgets w
     INNER
      JOIN
         shops_widgets sw
        ON
         w.widget_id = sw.widget_id
       AND
         sw.shop_id = 4
     INNER
      JOIN
         widget_params wp
        ON
         w.id = wp.widget_id
     INNER
      JOIN
         shop_widget_params swp
        ON
         wp.id = swp.widget_param_id
       AND
          sw.id = swp.shop_widget_id

    That should yield all widgets inside shop 4 with each widgets param and shop 4 value for the param.

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Code SQL:
    SELECT
         w.id
         ,wp.widget_param
         ,swp.param_value
      FROM
         widgets w
     INNER
      JOIN
         shops_widgets sw
        ON
         w.id = sw.widget_id
       AND
         sw.shop_id = 4
     INNER
      JOIN
         widget_params wp
        ON
         w.id = wp.widget_id
     INNER
      JOIN
         shop_widget_params swp
        ON
         wp.id = swp.widget_param_id
       AND
          sw.id = swp.shop_widget_id

    That should yield all widgets inside shop 4 with each widgets param and shop 4 value for the param.
    What's difference between this:
    Code MySQL:
    SELECT
        wp.widget_param,
        swp.param_value
    FROM
        shop_widget_params swp
        INNER JOIN shops_widgets sw ON swp.shop_widget_id = sw.id
        INNER JOIN widget_params wp ON swp.widget_param_id = wp.id
        INNER JOIN widgets w ON wp.widget_id = w.id AND sw.widget_id = w.id
        INNER JOIN shops s ON sw.shop_id = s.id
    WHERE
        s.id = 4
    Both gives the same results.

    Your inner join ordering is:
    => widgets, shops_widgets, widget_params & shop_widget_params
    And mine inner join ordering is:
    => shop_widget_params, shops_widgets, widget_params , widgets

    Very serious Question for me:
    Which ordering is perfect in case of multiple INNER JOINs?

  11. #11
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    I don't understand why you are talking about the perfect order. The DBMS will rearrange the order in which tables are accessed depending on the estimated selectivity for different search condition and the presence of suitable indexes.

  12. #12
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    I don't understand why you are talking about the perfect order. The DBMS will rearrange the order in which tables are accessed depending on the estimated selectivity for different search condition and the presence of suitable indexes.
    You mean the way oddz has performed & mine has no differences ?
    If there is no differences, then while writing multiple joins what's the convenient .
    Suppose for example:
    1> start with the parent table then its child ....
    2> start with the child table then its parent...
    etc..
    which convenient to follow? or which convenient do you follow?

  13. #13
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    I can't say that I have any convention. It's up to you to decide what is convenient for you.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i do have a convention -- i try to write the FROM clause in the order that makes logical sense, and this is often determined by the "driving" table, i.e. the table which has an associated WHERE condition

    for example, to find all books written by a given author, it would be
    Code:
      FROM authors
    INNER
      JOIN book_authors
        ON book_authors.author_id = book.id
    INNER
      JOIN books
        ON books.id = book_authors.book_id
     WHERE author.id = 42
    this sequence makes sense, don't you think?

    here's a different sequence --
    Code:
      FROM book_authors
    INNER
      JOIN books
        ON books.id = book_authors.book_id
    INNER
      JOIN authors
        ON book_authors.author_id = book.id
     WHERE author.id = 42
    two questions for you:

    1. what does this query do compared to the previous one?

    2. do you think it will perform differently?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i do have a convention -- i try to write the FROM clause in the order that makes logical sense, and this is often determined by the "driving" table, i.e. the table which has an associated WHERE condition

    for example, to find all books written by a given author, it would be
    Code:
      FROM authors
    INNER
      JOIN book_authors
        ON book_authors.author_id = book.id
    INNER
      JOIN books
        ON books.id = book_authors.book_id
     WHERE author.id = 42
    this sequence makes sense, don't you think?

    here's a different sequence --
    Code:
      FROM book_authors
    INNER
      JOIN books
        ON books.id = book_authors.book_id
    INNER
      JOIN authors
        ON book_authors.author_id = book.id
     WHERE author.id = 42
    two questions for you:

    1. what does this query do compared to the previous one?

    2. do you think it will perform differently?
    Thanks rudy for sharing your valuable convention.
    I apologize for my mistake: i mean to write convention but it was written convenient.

    Question of your ans:
    1> Later one is the style of mine i do. One thing i would like to note that.. Generally its my conventions( i used to think in this way).
    When i write
    Code:
    FROM
        table1
    INNER JOIN
        table2
    then there must be some join condition between table1 & table2... may be i am wrong
    and this was the case in your first query in which there was no any join condition between authors & book_authors .
    2> no idea, have to run the query

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PHPycho View Post
    ... in your first query in which there was no any join condition between authors & book_authors
    OMG, i am so sorry, my sample queries both had a typo!!

    the first query should be like this --
    Code:
      FROM authors
    INNER
      JOIN book_authors
        ON book_authors.author_id = authors.id
    INNER
      JOIN books
        ON books.id = book_authors.book_id
     WHERE author.id = 42
    the second query should be like this --
    Code:
     FROM book_authors
    INNER
      JOIN books
        ON books.id = book_authors.book_id
    INNER
      JOIN authors
        ON authors.id = book_authors.author_id 
     WHERE author.id = 42
    now which one makes more sense? which one will perform better?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To be frank, i used to start from the the child tables (which has parent ids) i.e. book_authors. So i used the later one style of coding irrelevant of where condition.

    Which one makes more sense:
    I think the first one.. since we have
    FROM
    author...
    ......
    WHERE author.id = ?

    But no idea about performance issues

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    a good optimizer will create the same execution plan

    it was a trick question

    but only for inner joins -- for outer joins, sequence ~does~ make a difference

    if you have ever tried to read and understand someone else's complex query, being able to "see" what the query is trying to do, what the logic is, based on how it is written -- this is very advantageous

    when you write sql, remember, the person that has to read it and understand it later is you

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

  19. #19
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a good optimizer will create the same execution plan

    it was a trick question

    but only for inner joins -- for outer joins, sequence ~does~ make a difference

    if you have ever tried to read and understand someone else's complex query, being able to "see" what the query is trying to do, what the logic is, based on how it is written -- this is very advantageous

    when you write sql, remember, the person that has to read it and understand it later is you

    Thanks rudy for your suggestion.

    But you haven't explained on performances.

    Rudy, is there any tutorials for mastering INNER JOINS ?
    Thanks once again

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, there are tons of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, there are tons of them
    Though there may be.
    But i want the choice of yours.
    Would you not mind pointing some
    Thanks

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    to be honest, i don't have a choice for tutorial about joins, i never needed one, because i've known how to do inner joins since before there was a world wide web

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

  23. #23
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    to be honest, i don't have a choice for tutorial about joins, i never needed one, because i've known how to do inner joins since before there was a world wide web

    I think i should go with Simply SQL

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i was ~not~ going to bring it up, but since you did, let me suggest that you read chapter 3 about the various types of join

    here --> http://articles.sitepoint.com/articl...he-from-clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was ~not~ going to bring it up, but since you did, let me suggest that you read chapter 3 about the various types of join

    here --> http://articles.sitepoint.com/articl...he-from-clause
    Thanks rudy for sharing the link.
    I am going to bookmark that link, seems interesting


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
  •