dear all.i newbie at web programming and until now i still have learn about MySQL syntax.
for now i start to use LEFT JOIN
method. i know that this method use for make normalization between two or many tables. I have posted a question in SO, then I receive an Answer which make me confuse. I have modified that answer,but i still confuse because it use LEFT JOIN
just for one table. whether the LEFT JOIN
can be used in one table?
views:
37answers:
1You can LEFT JOIN
a table with itself, just like you can JOIN
a table with itself. Your purpose will usually be different, because the specific characteristic of a LEFT JOIN
is ensuring a row in the output when no corresponding row exists on the right, of course; you can select those rows which have specifically been selected that way by checking for NULL
for the "other part" of the row, the part that would normally come from the right-side table.
Consider for example a table Product
with columns ID
, primary key, Name
, Category
, and Cost
; you want info about products that are cheapest in their category. Then...:
SELECT P1.Name, P1.Category, P1.Cost
FROM Product AS P1
LEFT JOIN Product AS P2
ON (P1.Category = P2.Category and P1.Cost > P2.Cost)
WHERE P2.ID IS NULL
is an example of a "left join of a table to itself" which will answer the "you want" spec (if more than one item has the equal-lowest cost in a category you'll get them all -- the query actually gives you the items such that no item in their category is cheaper and has no checks for items of equal cost;-).