SQLTeam.com | Weblogs | Forums

3 tables return all records where not in 3rd table?


#1

I have 3 Tables as follows:

TableD
Columns: DownDays
1/1/2017
7/4/2017
9/4/2017

TableW
Columns: WorkCenterId
WC1
WC2
WC3

TableWD
Columns: WorkCenterId, DownDays
WC1, 1/1/2017
WC1, 9/4/2017
WC3, 1/1/2017
WC3, 7/4/2017

TableD contains a list of ALL the downdays for the company (days off/closed). TableW contains all the WorkCenters for the Company. TableWD contains all the WorkCenters and respective DownDays for that WorkCenter. I'm trying to get a list of All the DownDays and WorkCenterId's that are NOTcurrently in the TableWD

So I am trying to get something like this:
WC1, 7/4/2017
WC2, 1/1/2017
WC2, 7/4/2017
WC2, 9/4/2017
WC3, 1/1/2017
WC3, 9/4/2017

Any assistance is greatly appreciated!


#2
SELECT
	*
FROM
	TableD AS d
	CROSS JOIN TableW AS w
WHERE 
	NOT EXISTS
    (
		SELECT * 
		FROM TableWD wd
		WHERE
			wd.WorkCenterId = w.WorkCenterId
			AND wd.DownDays = d.DownDays
	);