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/07/21 15:36] (current) – joetulenko
Line 2: Line 2:
  
 Find here some suggestions on how to structure your queries, some things to be aware of when making queries, and some examples of useful queries you can directly copy and paste into your SQL program that should work in ICE-D. Find here some suggestions on how to structure your queries, some things to be aware of when making queries, and some examples of useful queries you can directly copy and paste into your SQL program that should work in ICE-D.
 +
 +**07/21/26 update: Joe has gained more experience and will be updating this page to reflect that soon.**
  
 \\ \\
Line 91: Line 93:
 For example, **samples** and **publications** are linked through a two-way relation because it is possible for a publication to have many samples (it better, right? It would hopefully be tough to publish a paper with only ONE exposure age reported in it), but it is also possible for one sample to be referenced in multiple publications (perhaps a research group ran a sample back in the day, but recently re-ran another aliquot of the same exact sample and discussed that sample in a new publication). For example, **samples** and **publications** are linked through a two-way relation because it is possible for a publication to have many samples (it better, right? It would hopefully be tough to publish a paper with only ONE exposure age reported in it), but it is also possible for one sample to be referenced in multiple publications (perhaps a research group ran a sample back in the day, but recently re-ran another aliquot of the same exact sample and discussed that sample in a new publication).
  
-Thus, IF YOU DO NOT WANT TO SELECT INFORMATION FROM A SAMPLE MORE THAN ONCE and it shows up in multiple publications, I think it can be useful to use the DISTINCT statement after SELECT (see examples of DISTINCT in use below).+Thus, IF YOU DO NOT WANT TO SELECT INFORMATION FROM A SAMPLE MORE THAN ONCE and it shows up in multiple publications, I think it can be useful to use the DISTINCT statement after SELECT (see examples of DISTINCT in use below). You can also use the CONCAT statement to print out multiple entries from one table that are linked to a single entry in another. For example, if a sample is associated with multiple publications, you can use CONCAT to print out all of the DOIs from all the papers that sample is associated with in a single row instead of printing duplicate rows of the same sample for each paper it's linked to.
  
 \\ \\
Line 308: Line 310:
  
 \\ \\
 +
 +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>