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 [2023/10/19 21:46] – 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 262: Line 262:
  
 \\ \\
 +
 +7. Filtering selection by choosing samples from sites that have AT LEAST 3 samples per site. For example, let's say I only trust moraine ages in the database that are based on at least 3 boulder ages on the moraine, any less and the age might be uncertain.\\
 +**New Feature:** lots of new features in this one, but mainly the GROUP_BY and HAVING COUNT (*) built-in functions.
 +
 +<code>
 +
 +SELECT base_sample.name, base_sample.lat_DD, base_sample.lon_DD
 +FROM base_sample
 +JOIN base_site ON base_site.id = base_sample.site_id
 +WHERE base_site.what LIKE "%moraine%"
 +AND base_sample.site_id IN
 +(SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id HAVING COUNT(*) > 2)
 +
 +</code>
 +
 +//note: the statement before and within the parentheses (I think) works more or less like this:\\
 +1. "AND base_sample.site_id IN" = give me all samples where their site_id field meets the following criteria.\\
 +2."SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id" = within the parentheses argument, select the site_id field from the samples table and group all entries in the samples table by their site_id. \\
 +3. "HAVING COUNT(*) > 2" = once samples are grouped by their site_id, find and select only samples where the number of samples per respective site is greater than 2 (ie at least 3).//
 +
 +----
 +
 +\\
 +
 +8. Query used to filter the ICE-D Alpine application for samples in the Cascades and Sierra Nevada as part of Tulenko et al. (2024): Cosmogenic 21Ne exposure ages on late Pleistocene moraines in Lassen Volcanic National Park, California, USA.
 +
 +<code>
 +
 +SELECT base_sample.name, base_site.short_name, base_calculatedage.t_LSDn, base_calculatedage.dtint_LSDn, base_sample.lat_DD, base_sample.lon_DD, base_calculatedage.nuclide
 +FROM base_sample
 +JOIN base_site ON base_sample.site_id = base_site.id
 +JOIN base_calculatedage ON base_calculatedage.sample_id = base_sample.id
 +WHERE base_sample.what LIKE "%oulder%"
 +AND base_site.what LIKE "%oraine%"
 +AND base_sample.site_id IN
 +(SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id HAVING COUNT(*) > 3)
 +AND (base_sample.lat_DD > 35 AND base_sample.lat_DD < 48)
 +AND (base_sample.lon_DD > -123 AND base_sample.lon_DD < -117)
 +AND (base_calculatedage.t_LSDn > 10000 AND base_calculatedage.t_LSDn < 35000)
 +ORDER BY base_sample.lat_DD
 +
 +</code>
 +
 +----
 +
 +\\
 +
 +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>