update code

Qian-Yu-2020 · Qian-Yu-2020 · commit 8f5425fabae1 · 2020-11-09T23:49:58.000-05:00
diff --git a/Order and Deliver/# 607. Sales Person.sql b/Order and Deliver/# 607. Sales Person.sql
@@ -1,6 +1,8 @@
 # 607. Sales Person
 
 # Output all the names in the table salesperson, who didn’t have sales to company 'RED'
+# Don't forgot to use DISTINCT to avoid duplicate
+
 
 SELECT DISTINCT s.name AS name 
 FROM salesperson s 
@@ -9,3 +11,10 @@ WHERE s.sales_id NOT IN (
     FROM orders o 
     JOIN company c ON o.com_id = c.com_id WHERE c.name = "RED")
 
+# or 
+# Will join three table in a row is easier to understand
+SELECT DISTINCT p.name AS name
+FROM salesperson p LEFT JOIN orders o USING sales_id 
+LEFT JOIN company c USING com_id
+WHERE c.name != “RED”
+;
diff --git a/Schools and Organizations/# 1350. Students With Invalid Departments.sql b/Schools and Organizations/# 1350. Students With Invalid Departments.sql
@@ -1,7 +1,16 @@
 # 1350. Students With Invalid Departments
-
+# Find the id and the name of all students who are enrolled in departments that no longer exists
 SELECT s.id, s.name 
 FROM Students s 
+# Because the SELECT information is from Student table
     LEFT JOIN Departments d ON d.id = s.department_id
 WHERE d.name IS NULL
 ;
+
+# or
+# Has to use LEFT JOIN?
+SELECT s.id, s.name
+FROM department d, students s
+WHERE d.id = s.department_id
+AND d.name IS NULL 
+;
