SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inner join to > 1 table. OK??

    I think I have forgotten something.

    I am querying several tables all with inner joins. if I use distinct, I get 16 rows returned.

    If I don't, I get over 800.

    if I add a few extra clauses to one of the inner joins, I get the correct resultset, without distinct and more quikcly than when using distinct.

    But is it ok to have an inner join (or any join for that matter), relating to two or more tables.

    Notice how the last two conditions relate to a different table from the first two.

    bazz
    Code MySQL:
     
    inner
        join control_panel_data_business_sub_types as cpdbst
          on cpdbst.business_type = btc.business_type
         and cpdbst.sub_type = btc.sub_type
         and cpdbst.cp_page_grouping = cpbd.cp_page_grouping
         and cpdbst.page_category = cpbd.page_category

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Where is cpbd being included in this statement? What's the rest of the FROM clause?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for reading.

    I moved those two lines down to the WHERE clause because they weren't part of that JOIN.

    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    bazz, please show the entire query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    It seems to work as necessary but here it is, as requested, because I can always be shown how to improve.

    Also, I found it quite difficult because I was using natural keys rather than whatchacallem, oh yeh, surrogate keys.

    Code MySQL:
    select cpdg.sequence_number
           , cpdg.cp_page_grouping
           , cpdc.sequence_number
           , cpdc.page_category
        from control_panel_data_groups as cpdg	
      inner
        join control_panel_data_categories as cpdc
    	  on cpdc.control_panel_section = cpdg.control_panel_section
    	 and cpdc.cp_page_grouping = cpdg.cp_page_grouping
      inner
        join control_panel_business_datagroups as cpbd
          on cpbd.control_panel_section = cpdc.control_panel_section
         and cpbd.cp_page_grouping = cpdc.cp_page_grouping
         and cpbd.page_category = cpdc.page_category
     
    inner
        join business_types_complete as btc
          on btc.business_id = cpbd.business_id
    inner
        join control_panel_data_business_sub_types as cpdbst
          on cpdbst.business_type = btc.business_type
         and cpdbst.sub_type = btc.sub_type
     
       where cpbd.business_id = ?
         and cpbd.control_panel_section = 'Page Content Editor'
    	 and cpdbst.cp_page_grouping = cpbd.cp_page_grouping
    	 and cpdbst.page_category = cpbd.page_category
    Last edited by IBazz; Apr 25, 2011 at 02:48. Reason: additional info

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, now we can get back to your original question

    is it okay to have an inner join relating to two or more tables?

    yes

    it seems you have misplaced a couple of your join conditions into the WHERE clause, so let's move them back to where they belong, in the ON clause of the appropriate join
    Code:
    SELECT cpdg.sequence_number
         , cpdg.cp_page_grouping
         , cpdc.sequence_number
         , cpdc.page_category
      FROM control_panel_business_datagroups         AS cpbd
    INNER
      JOIN control_panel_data_categories             AS cpdc
        ON cpdc.control_panel_section = cpbd.control_panel_section 
       AND cpdc.cp_page_grouping      = cpbd.cp_page_grouping     
       AND cpdc.page_category         = cpbd.page_category        
    INNER
      JOIN control_panel_data_groups                 AS cpdg
        ON cpdg.control_panel_section = cpdc.control_panel_section  
       AND cpdg.cp_page_grouping      = cpdc.cp_page_grouping     
    INNER
      JOIN business_types_complete                   AS btc
        ON btc.business_id            = cpbd.business_id
    INNER
      JOIN control_panel_data_business_sub_types     AS cpdbst
        ON cpdbst.business_type       = btc.business_type
       AND cpdbst.sub_type            = btc.sub_type
       AND cpdbst.cp_page_grouping    = cpbd.cp_page_grouping
       AND cpdbst.page_category       = cpbd.page_category
     WHERE cpbd.business_id = ?
       AND cpbd.control_panel_section = 'Page Content Editor'
    notice how the FROM clause commences with the "driving" table, i.e. the table that has the restrictions put on it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy.


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
  •