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

Click to view Full table

Sample table: Production.workorderrouting

Click to view Full table

Sample Solution:

SELECT a.productid, a.startdate 
FROM production.workorder AS a  
WHERE EXISTS  
(SELECT *   
    FROM production.workorderrouting  AS b  
    WHERE (a.productid = b.productid and a.startdate=b.actualstartdate)) ;

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.