13.2 Joins

13.2.1 JOIN TABLES IN SQL WITH DBI

DBI::dbGetQuery(my_db_con, 
                "SELECT * 
                 FROM rap_chart_artists a 
                 JOIN rap_chart_singles b ON a.\"Artist_ID\" = b.\"Artist_ID\"")

13.2.2 JOIN TABLES IN SQL IN SQL CHUNK

```{sql connection=my_db_con}
SELECT *
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
```

13.2.3 JOIN TABLES IN SQL IN SQL CHUNK WITH OUTPUT VARIABLE

```{sql connection=my_db_con, output.var="rap_artist_singles"}
SELECT *
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
```

rap_artist_singles[, c("First_Name", "Last_Name")]

13.2.4 JOIN TABLES IN SQL IN SQL CHUNK APPLY WHERE CLAUSE AND FUZZY SEARCH

```{sql connection=my_db_con}
SELECT *
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
WHERE "Psuedonym" LIKE '%LL%'
```

13.2.5 JOIN TABLES IN SQL IN SQL CHUNK APPLY WHERE CLAUSE WITH ‘AND’ AND FUZZY SEARCH

```{sql connection=my_db_con}
SELECT *
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
WHERE "Psuedonym" LIKE '%LL%'
AND "Top_US_Rap_Chart" IS NOT NULL
```

13.2.6 JOIN TABLES IN SQL IN SQL CHUNK APPLY SELECT AND WHERE CLAUSE WITH ‘AND’ AND FUZZY SEARCH AND ORDER

```{sql connection=my_db_con}
SELECT "Psuedonym", "Single", "Year", "Top_US_Rap_Chart"
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
WHERE "Psuedonym" LIKE '%LL%'
AND "Top_US_Rap_Chart" IS NOT NULL
ORDER BY "Top_US_Rap_Chart", "Year"
```

13.2.7 JOIN TABLES IN SQL IN SQL CHUNK APPLY SELECT AND WHERE CLAUSE AND FUZZY SEARCH AND ORDER AND GROUP BY WITH AGGREGATE FUNCTION

```{sql connection=my_db_con, max.print = 15}
SELECT "Psuedonym", "Top_US_Rap_Chart", COUNT("Top_US_Rap_Chart") chart_position_cnt
FROM rap_chart_artists a
JOIN rap_chart_singles s ON a."Artist_ID" = s."Artist_ID"
WHERE "Psuedonym" LIKE '%M.C.%'
AND "Top_US_Rap_Chart" IS NOT NULL
GROUP BY "Psuedonym", "Top_US_Rap_Chart"
ORDER BY "Psuedonym", "Top_US_Rap_Chart"
```