AdventureWorks Database: Return rows except two values in the two tables match
189. From the following tables write a query in SQL to return rows except both the productid and startdate values in the two tables matches.
Sample table: Production.workorder
workorderid|productid|orderqty|scrappedqty|startdate |enddate |duedate |scrapreasonid|modifieddate |
-----------+---------+--------+-----------+-----------------------+-----------------------+-----------------------+-------------+-----------------------+
1| 722| 8| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
2| 725| 15| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
3| 726| 9| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
4| 729| 16| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
5| 730| 14| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
6| 732| 16| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
7| 733| 4| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
8| 738| 19| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
9| 741| 2| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
10| 742| 3| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
11| 743| 1| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
12| 745| 1| 0|2011-06-03 00:00:00.000|2011-06-13 00:00:00.000|2011-06-14 00:00:00.000| |2011-06-13 00:00:00.000|
-- more --
Sample table: Production.workorderrouting
workorderid|productid|operationsequence|locationid|scheduledstartdate |scheduledenddate |actualstartdate |actualenddate |actualresourcehrs|plannedcost|actualcost|modifieddate |
-----------+---------+-----------------+----------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------+-----------+----------+-----------------------+
13| 747| 1| 10|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 4.1000| 92.25| 92.25|2011-06-19 00:00:00.000|
13| 747| 2| 20|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 3.5000| 87.5| 87.5|2011-06-19 00:00:00.000|
13| 747| 3| 30|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 1.0000| 14.5| 14.5|2011-06-19 00:00:00.000|
13| 747| 4| 40|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 2.0000| 31.5| 31.5|2011-06-19 00:00:00.000|
13| 747| 6| 50|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 3.0000| 36.75| 36.75|2011-06-19 00:00:00.000|
13| 747| 7| 60|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 4.0000| 49| 49|2011-06-19 00:00:00.000|
14| 748| 1| 10|2011-06-03 00:00:00.000|2011-06-14 00:00:00.000|2011-06-03 00:00:00.000|2011-06-19 00:00:00.000| 4.1000| 92.25| 92.25|2011-06-19 00:00:00.000|
-- more --
Sample Solution:
-- Selecting columns productid and startdate from the production.workorder table and aliasing it as 'a'
SELECT a.productid, a.startdate
-- From the production.workorder table, aliased as 'a'
FROM production.workorder AS a
-- Filtering records where there doesn't exist a matching record in the workorderrouting table
WHERE NOT EXISTS
-- Subquery to check for the existence of a matching record in the workorderrouting table
(SELECT *
-- Selecting all columns from the production.workorderrouting table and aliasing it as 'b'
FROM production.workorderrouting AS b
-- Matching condition: productid and startdate must be the same in both tables
WHERE (a.productid = b.productid and a.startdate=b.actualstartdate)) ;
Explanation:
- This SQL query retrieves data from the workorder table in the Production schema.
- Comments are added to explain each part of the query for better understanding and maintenance.
- Here's a breakdown of what the query does:
- It selects the productid and startdate columns from the workorder table.
- The main query is used to filter out records where there is no matching record in the workorderrouting table.
- The NOT EXISTS clause is used to check for the absence of a matching record in the workorderrouting table.
- The subquery checks for the existence of a record in the workorderrouting table that matches the productid and startdate from the main query's workorder table.
- If no matching record is found, the record from the workorder table is included in the result set.
Sample Output:
productid|startdate |
---------+-----------------------+
3|2011-06-03 00:00:00.000|
316|2011-06-03 00:00:00.000|
324|2011-06-03 00:00:00.000|
327|2011-06-03 00:00:00.000|
328|2011-06-03 00:00:00.000|
329|2011-06-03 00:00:00.000|
331|2011-06-03 00:00:00.000|
350|2011-06-03 00:00:00.000|
398|2011-06-03 00:00:00.000|
399|2011-06-03 00:00:00.000|
400|2011-06-03 00:00:00.000|
401|2011-06-03 00:00:00.000|
529|2011-06-03 00:00:00.000|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return rows only when two values in the two tables match.
Next: Retrieve all rows, columns using table aliasing.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.