Is that possible to INNER JOIN more than one table?

Question

Using:

    SELECT * 
      FROM cart 
INNER JOIN dkb ON cart.id = dkb.id 
     WHERE cart.cookieId = '" . GetCartId() . "'"

Besides the dkb table, I want to INNER JOIN the cdkb table using ON cart.id = cdkb.id.

How would that be displayed when cdkb added to the existing query?

Answer 1

Something like this

SELECT *
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.ID = t2.ID INNER JOIN
Table3 t3 ON t1.ID = t3.ID INNER JOIN
....
TableN tn ON t1.ID = tn.ID

So it would look like

SELECT  * 
FROM    cart INNER JOIN 
        dkb ON cart.id = dkb.id INNER JOIN 
        cdkb ON cart.id = cdkb.id
WHERE cart.cookieId = '" . GetCartId() . "'

Answer 2

You can use as many JOIN as you want -- just add them to the from/join section of the query :

select *
from cart
    inner join dkb on cart.id = dkb.id
    inner joion cdkb on cart.id = cdkb.id
WHERE cart.cookieId = '...'

And, for the details, you can take a look at this section of the manual : 12.2.8.1. JOIN Syntax.

Answer 3

Exactly as you have described it:

SELECT 
   * 
FROM 
    cart 

    JOIN dkb 
       ON cart.id = dkb.id 

    JOIN cdkb 
       on cart.id = cdkb.id
WHERE 
   cart.cookieId = GetCartId()

regarding OUTER JOINs, here is a link discussing them:

if you do:

SELECT
   cart.id    cart_id,
   dkb.id     dkb_id,
   cdkb.id    cdkb_id,
   cart.*,
   dkb.*,
   cdkb.*
FROM
    cart

    LEFT OUTER JOIN dkb
       ON cart.id = dkb.id

    LEFT OUTER JOIN cdkb
       on cart.id = cdkb.id
WHERE 
    cart.cookieId = GetCartId()

That means you will always get every matching record from cart, information from the dkb and cdkb tables will only appear if records with that ID are available in them, otherwise their fields will be NULL. I used a LEFT OUTER JOIN because I want all the records from the cart table, which is on the LEFT side of the ON condition.

A FULL OUTER JOIN would mean that either the cart.* or the dkb or cdkb fields could be NULL.

It definitely takes awhile to get a feel for the OUTER JOIN syntax, as you can tell since I still got it wrong explaining it (the first time). :-)

Answer 4

Best practice would be to explicitly specify field names per table, and use an ALIAS on each table, e.g.

SELECT
 c.field1 AS c_field1
, c.field2 AS c_field2
, c.field3 AS c_field3
, d.field1 AS d_field1
, c2.field1 AS c2_field1
FROM cart AS c
INNER JOIN dkb AS d
  ON (c.id = d.id)
INNER JOIN ckdb AS c2
  ON (c2.id = c.id)
WHERE c.cookieID = '{VALUE}'

Of course this glib example doesn't let the merits of an alias shine; generally if you have a table name like CustomersBankAccountData, then an alias is ideal. ;)

EDITED: updated to show aliased field names, which makes perhaps more sense in this case than even aliasing the tables.

Answer 5

you may find this article helpful:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

is says MySQL, but it's pretty generic in the explanation of JOINS (INNER, LEFT and RIGHT)