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 [2024/12/03 17:29] – joetulenkopluginto:useful_sql_queries [2026/01/21 17:21] (current) – joetulenko
Line 100: Line 100:
 For example, someone might have data in ICE-D that is yet unpublished but fully measured. The samples can have calculated ages, but of course they are not linked to any publication entry in the publication table. Thus, if you want sample info AND publication info for a bunch of samples in an area of interest, but some of the samples in that area do NOT belong to a publication, those 'unpublished' samples WILL BE OMITTED FROM THE RESULTS. For example, someone might have data in ICE-D that is yet unpublished but fully measured. The samples can have calculated ages, but of course they are not linked to any publication entry in the publication table. Thus, if you want sample info AND publication info for a bunch of samples in an area of interest, but some of the samples in that area do NOT belong to a publication, those 'unpublished' samples WILL BE OMITTED FROM THE RESULTS.
  
-There is probably some work around to fix this, like selecting those samples even if they aren't linked to a publication and letting the pubication fields be NULL or something, but I haven't yet figured that out. Maybe a left or right join? Not sure. For the time being, just be aware this can happen and you might not be getting the representative selection you are hoping for.+The way around this is through a LEFT JOIN (see below).
  
 ---- ----
Line 171: Line 171:
 </code> </code>
  
-//note: in this case, since I wasn't looking for sample info at all but rather JUST info from the 14C nuclide table, I use the FROM _c14_quartz statement NOT FROM base_sample//+//note: in this case, since I wasn't looking for sample info at all but rather JUST info from the 14C nuclide table, I use the FROM _c14_quartz statement not FROM base_sample//
  
 ---- ----
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>