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 17:19] – 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 311: Line 313:
 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. 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:+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) \\ (1) samples that only have 10Be concentrations (and/or 26Al concentrations) \\