Inner join to > 1 table. OK?

Databases
1

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



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

Where is cpbd being included in this statement? What’s the rest of the FROM clause?

3

Thanks for reading.

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

bazz

4

bazz, please show the entire query

5

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.


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
6

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 :slight_smile:

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


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

7

Thanks rudy.