meta data for this page
  •  

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
pluginto:useful_sql_queries [2026/01/21 16:48] – joetulenkopluginto:useful_sql_queries [2026/01/21 17:21] (current) – joetulenko
Line 308: Line 308:
  
 \\ \\
 +
 +9. LEFT JOIN - let's say I want info on a bunch of samples from multiple fields/tables in ICE-D but some samples may not have all of the information in every field in my query. Using a standard JOIN, samples that are missing info from one or more of my selected fields may not be included in my results list. However, using a LEFT JOIN you can select samples that match the filtering criteria and have information filled in for at least one of the selected fields in my query.
 +
 +In the example below, I am searching for sample names, sample 10Be (and/or 26Al) concentrations, and sample 14C concentrations for alpine glacier samples across the Western United States. **//Using a LEFT JOIN, I will select samples that satisfy one of the following criteria://**
 +
 +(1) samples that only have 10Be concentrations (and/or 26Al concentrations) \\
 +(2) samples that only have 14C concentrations \\
 +(3) samples that have both 10Be (and/or 26Al concentrations) AND 14C concentrations \\
 +(4) samples have none of the above - likely because a sample is linked to an incomplete entry in either the 10Be/26Al table and/or the 14C table that's missing nuclide concentration(s) \\
 +
 +(using a standard JOIN would only select samples that satisfy criteria 3 and in rare cases 4)
 +
 +<code>
 +
 +SELECT base_sample.name, _be10_al26_quartz.N10_atoms_g, _be10_al26_quartz.N26_atoms_g, _c14_quartz.N14_atoms_g
 +FROM base_sample
 +LEFT JOIN _be10_al26_quartz ON _be10_al26_quartz.sample_id = base_sample.id
 +LEFT JOIN _c14_quartz ON _c14_quartz.sample_id = base_sample.id
 +WHERE (base_sample.lat_DD > 31 AND base_sample.lat_DD < 49)
 +AND (base_sample.lon_DD > -125 AND base_sample.lon_DD < -104)
 +
 +</code>