AdventureWorks Database: Return rows only when two values in the two tables match
SQL Query - AdventureWorks: Exercise-188 with Solution
188. From the following tables write a query in SQL to return rows only when 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, aliasing the table as 'a'
SELECT a.productid, a.startdate
-- From the production.workorder table, aliasing the table as 'a'
FROM production.workorder AS a
-- Filtering records based on the existence of related records in another table
WHERE EXISTS
(
-- Subquery to check for the existence of records in the production.workorderrouting table, aliasing it as 'b'
SELECT *
-- From the production.workorderrouting table, aliasing the table as 'b'
FROM production.workorderrouting AS b
-- Condition to match records from the outer query with the subquery
WHERE (a.productid = b.productid and a.startdate = b.actualstartdate)
) ;
Explanation:
- This SQL query retrieves data from the workorder table in the production schema based on a condition involving related records in the workorderrouting table.
- 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, aliasing the table as 'a'.
- The WHERE clause includes a subquery using the EXISTS keyword to check for the existence of related records in the workorderrouting table, aliased as 'b'.
- The condition inside the subquery matches records from the outer query with records in the workorderrouting table based on the productid and startdate fields.
- If a matching record is found in the workorderrouting table for a record in the workorder table, the EXISTS condition is true, and the corresponding record from the workorder table is returned.
Sample Output:
productid|startdate |
---------+-----------------------+
747|2011-06-03 00:00:00.000|
748|2011-06-03 00:00:00.000|
749|2011-06-03 00:00:00.000|
753|2011-06-03 00:00:00.000|
754|2011-06-03 00:00:00.000|
755|2011-06-03 00:00:00.000|
756|2011-06-03 00:00:00.000|
758|2011-06-03 00:00:00.000|
760|2011-06-03 00:00:00.000|
761|2011-06-03 00:00:00.000|
762|2011-06-03 00:00:00.000|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Null special offers will return MaxQty as zero.
Next: Return rows except two values in the two tables match.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-188.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics