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?
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,
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;-).