AdventureWorks Database: Return rows only when two values in the two tables match
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics