w3resource

AdventureWorks Database: Return rows except two values in the two tables match

SQL Query - AdventureWorks: Exercise-189 with Solution

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


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 not EXISTS  
(SELECT *   
    FROM production.workorderrouting  AS b  
    WHERE (a.productid = b.productid and a.startdate=b.actualstartdate)) ;

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv