Perfect INNER JOIN query on multiple tables

Databases
1

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:

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

2

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?

3

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

I’m not sure if that would work for what you need it to do:

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.

5

First of all sorry for the late reply.
If i write following query

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

AND sw.widget_id = w.id

in #* line

6 
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

Are the users not getting my post meaning ? :frowning:

8

Post a create table query and some sample data.

9 

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

What’s difference between this:

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

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

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

I can’t say that I have any convention. It’s up to you to decide what is convenient for you. :slight_smile:

14

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


  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 –


  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?

15

Thanks rudy for sharing your valuable convention.
I apologize for my mistake: i mean to write convention but it was written convenient.:slight_smile:

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

FROM
    table1
INNER JOIN
    table2

then there must be some join condition between table1 & table2… may be i am wrong :slight_smile:
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

OMG, i am so sorry, my sample queries both had a typo!!

the first query should be like this –


  FROM authors
INNER
  JOIN book_authors
    ON book_authors.author_id = [COLOR="Blue"]authors[/COLOR].id
INNER
  JOIN books
    ON books.id = book_authors.book_id
 WHERE author.id = 42

the second query should be like this –


 FROM book_authors
INNER
  JOIN books
    ON books.id = book_authors.book_id
INNER
  JOIN authors
    ON [COLOR="blue"]authors[/COLOR].id = book_authors.author_id
 WHERE author.id = 42

now which one makes more sense? which one will perform better?

17

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

18

a good optimizer will create the same execution plan

it was a trick question :wink:

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

:cool:

19

Thanks rudy for your suggestion.

But you haven’t explained on performances.

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

20

yes, there are tons of them