Subqueries

13.6. Subqueries#

13.6.1. Using value from a subquery#

SELECT p.*, m.max_stay
FROM  PatientCorePopulatedTable  p
INNER JOIN 
(SELECT PatientID, ROUND(MAX(julianday(AdmissionEndDate)-julianday(AdmissionStartDate))) MAX_STAY
FROM AdmissionsCorePopulatedTable 
GROUP BY PatientID
HAVING MAX_STAY >= 15
) m
ON p.PatientID = m.PatientID
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('100_patients.db')

sql_statement = """
SELECT p.*, m.max_stay
FROM  PatientCorePopulatedTable  p
INNER JOIN 
(SELECT PatientID, ROUND(MAX(julianday(AdmissionEndDate)-julianday(AdmissionStartDate))) MAX_STAY
FROM AdmissionsCorePopulatedTable 
GROUP BY PatientID
HAVING MAX_STAY >= 15
) m
ON p.PatientID = m.PatientID
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[1], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('100_patients.db')
      6 sql_statement = """
      7 SELECT p.*, m.max_stay
      8 FROM  PatientCorePopulatedTable  p
   (...)     15 ON p.PatientID = m.PatientID
     16 """

ModuleNotFoundError: No module named 'pandas'

13.6.2. IN and NOT IN examples#

SELECT *
FROM  LabsCorePopulatedTable 
WHERE PatientID IN (
    SELECT PatientID 
    FROM PatientCorePopulatedTable 
    WHERE PatientLanguage IN ('Icelandic', 'Spanish')
)
LIMIT 100;
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('100_patients.db')

sql_statement = """
SELECT *
FROM  LabsCorePopulatedTable 
WHERE PatientID IN (
    SELECT PatientID 
    FROM PatientCorePopulatedTable 
    WHERE PatientLanguage IN ('Icelandic', 'Spanish')
)
LIMIT 100;
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[2], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('100_patients.db')
      6 sql_statement = """
      7 SELECT *
      8 FROM  LabsCorePopulatedTable 
   (...)     14 LIMIT 100;
     15 """

ModuleNotFoundError: No module named 'pandas'

SELECT *
FROM  LabsCorePopulatedTable 
WHERE PatientID IN (
    SELECT PatientID 
    FROM PatientCorePopulatedTable 
    WHERE PatientLanguage NOT IN ('Icelandic', 'Spanish')
)
LIMIT 100;
Hide code cell source 
import sqlite3
import pandas as pd

conn = sqlite3.connect('100_patients.db')

sql_statement = """
SELECT *
FROM  LabsCorePopulatedTable 
WHERE PatientID IN (
    SELECT PatientID 
    FROM PatientCorePopulatedTable 
    WHERE PatientLanguage NOT IN ('Icelandic', 'Spanish')
)
LIMIT 100;
"""
df = pd.read_sql_query(sql_statement, conn)
df.style.set_table_attributes('style="font-size: 12px"')

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[3], line 2
      1 import sqlite3
----> 2 import pandas as pd
      4 conn = sqlite3.connect('100_patients.db')
      6 sql_statement = """
      7 SELECT *
      8 FROM  LabsCorePopulatedTable 
   (...)     14 LIMIT 100;
     15 """

ModuleNotFoundError: No module named 'pandas'
Contents