27341

Learning LEFT JOIN in MySQL

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?

Answer1:

You 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;-).

Recommend

  • MySQL Select multiple rows from one row in 2 different tables
  • Flink Kafka - how to make App run in Parallel?
  • Bluetooth LE TX Power reading
  • How to detect if a iphone is connected to any bluetooth device or not?
  • Converting Julian date to Java date but still cannot get Month
  • choosing right approch to implement google map in android
  • Get some trounble when using drawBuffers in WebGL2
  • Why can't I use the keyword “this” when referring to fields in static methods?
  • cannot load gems in test environment
  • php show all images in directory and sort by last modified
  • EF 4.1 DBContext AutoDetectChangesEnabled
  • Android device acting as an accessory
  • Implicit joins and Where in Doctrine - how?
  • I18n locale disregarding fallbacks
  • converting text file into xml using php?
  • What's the purpose of QString?
  • Excel - Autoshape get it's name from cell (value)
  • vba code to select only visible cells in specific column except heading
  • How can I use Kendo UI with Razor?
  • ORA-29908: missing primary invocation for ancillary operator
  • Do I've to free mysql result after storing it?
  • Jquery - Jquery Wysiwyg return html as a string
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Arrays break string types in Julia
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • Matrix multiplication with MKL
  • WPF Applying a trigger on binding failure
  • CSS Applying specific rule for a specific monitor resolution with only CSS is posible?
  • How do you join a server to an Active Directory (domain)?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Trying to get generic when generic is not available
  • Why joiner is not used after Sequence generator or Update statergy
  • Java static initializers and reflection
  • How does Linux kernel interrupt the application?
  • Sorting a 2D array using the second column C++
  • Converting MP3 duration time