Is that possible to INNER JOIN more than one table?
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 w开发者_如何学JAVAhen cdkb added to the existing query?
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() . "'
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.
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.
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). :-)
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)
精彩评论