Perfect INNER JOIN query on multiple tables

**PHPycho** · Nov 13, 2009 22:20

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

**r937** · Nov 13, 2009 22:48

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?

**PHPycho** · Nov 13, 2009 23:11

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.

**SpacePhoenix** · Nov 14, 2009 01:46

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.

**PHPycho** · Nov 16, 2009 02:35

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

**PHPycho** · Nov 16, 2009 05:55

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

**PHPycho** · Nov 16, 2009 09:49

Are the users not getting my post meaning ?

**SpacePhoenix** · Nov 16, 2009 13:00

Originally Posted by PHPycho

Are the users not getting my post meaning ?

Post a create table query and some sample data.

**oddz** · Nov 17, 2009 22:22

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.

**PHPycho** · Nov 18, 2009 02:05

Originally Posted by oddz

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?

**swampBoogie** · Nov 18, 2009 06:06

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.

**PHPycho** · Nov 19, 2009 00:24

Originally Posted by swampBoogie

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?

**swampBoogie** · Nov 19, 2009 06:23

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

**r937** · Nov 19, 2009 08:30

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?

**PHPycho** · Nov 19, 2009 10:09

Originally Posted by r937

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

**r937** · Nov 19, 2009 10:20

Originally Posted by PHPycho

... 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?

**PHPycho** · Nov 19, 2009 10:44

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

**r937** · Nov 19, 2009 12:58

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

**PHPycho** · Nov 20, 2009 01:42

Originally Posted by r937

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

**r937** · Nov 20, 2009 06:38

yes, there are tons of them

**PHPycho** · Nov 20, 2009 09:49

Originally Posted by r937

yes, there are tons of them

Though there may be.
But i want the choice of yours.
Would you not mind pointing some

Thanks

**r937** · Nov 20, 2009 11:07

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

**PHPycho** · Nov 20, 2009 11:12

Originally Posted by r937

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

**r937** · Nov 20, 2009 11:31

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

**PHPycho** · Nov 20, 2009 12:17

Originally Posted by r937

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

User Tag List

Thread: Perfect INNER JOIN query on multiple tables

Thread Tools

Display

Perfect INNER JOIN query on multiple tables

Bookmarks

Bookmarks

Posting Permissions