SQL Query for what is NOT in the join.

[MCTampa]

I have the following query, which you all helped me write a few years ago.
The query joins two tables and let me me know what units satisfy the parameters.
I want to modify the query to provide me with the units which DO NOT satisfy the parameters.

Thanks

SELECT 
mvwvse.RAREA, 
mvwvse.Developer, 
mvwvse.Resort, 
mvwvse.[Start Date], 
mvwvse.[End Date], 
mvwvse.SWeek, 
mvwvse.EWeek, 
mvwvse.[Start Year], 
mvwvse.[End Year], 
ic.Size, ic.TRKFLD, 
ic.SpaceType, 
ic.QRR, 
ic.TDI, 
Sum(ic.[Units]) AS Units

FROM MVWVSE AS mvwvse LEFT JOIN Inventory AS ic ON (mvwvse.Resort = ic.RESX) AND (ic.EXDTFDTF >= mvwvse.[Start Date]) AND (ic.EXDTFDTF <= mvwvse.[End Date])

WHERE (((ic.[Units]) Is Not Null))

GROUP BY mvwvse.RAREA, mvwvse.Developer, mvwvse.Resort, ic.Size, ic.TRKFLD, ic.QRR, ic.TDI, ic.SpaceType, mvwvse.[Start Date], mvwvse.[End Date], mvwvse.SWeek, mvwvse.Eweek, mvwvse.[Start Year], mvwvse.[End Year];

 

[MCTampa]

Disregard, I realize now this is a stupid post. I have to basically reverse the join.

 

[MCTampa]

In my original post, my query was asking for all of the units from the Inventory table that satisfied the criterial of the MVWVSE table.
What I want is all of the units from the Inventory table that fail the criteria of the MVWVSE table.

I reset my query, but want I want is to return all the values that do not satsify the below FROM line.

Does that make more sense?

SELECT ic.Developer, ic.RESX, ic.EXDTFDTF, ic.Year, ic.Week, ic.Area, ic.TRKFLD, ic.Size, ic.SpaceType, ic.[All Inclusive], ic.QRR, ic.TDI, Sum(ic.[Units]) AS Units
FROM Inventory AS ic LEFT JOIN MVWVSE AS mvwvse ON (mvwvse.Resort = ic.RESX) AND (ic.EXDTFDTF <= mvwvse.[End Date]) AND (ic.EXDTFDTF >= mvwvse.[Start Date])
WHERE (((ic.[Units]) Is Not Null))
GROUP BY ic.Developer, ic.RESX, ic.EXDTFDTF, ic.Year, ic.Week, ic.Area, ic.TRKFLD, ic.Size, ic.SpaceType, ic.[All Inclusive], ic.QRR, ic.TDI;

 

[Joe4]

Take a look here and see if it helps you out.

SQL OUTER JOIN - Left Join, Right Join and Full Outer Join

This tutorial shows you how to use SQL OUTER JOIN clauses including left join, right join and full outer join to query data from multiple tables.

www.zentut.com

 

[MCTampa]

And I figured it out.
Thanks anyway!

SELECT ic.Developer, ic.RESX, ic.EXDTFDTF, ic.Year, ic.Week, ic.Area, ic.TRKFLD, ic.Size, ic.SpaceType, ic.[All Inclusive], ic.QRR, ic.TDI, Sum(ic.[Units]) AS Units
FROM Inventory as ic
Where
not exists (
select mvwvse.resort, mvwvse.[Start Date], mvwvse.[End Date]
From mvwvse
Where mvwvse.Resort = ic.RESX AND ic.EXDTFDTF <= mvwvse.[End Date] AND ic.EXDTFDTF >= mvwvse.[Start Date]
)
GROUP BY ic.Developer, ic.RESX, ic.EXDTFDTF, ic.Year, ic.Week, ic.Area, ic.TRKFLD, ic.Size, ic.SpaceType, ic.[All Inclusive], ic.QRR, ic.TDI;

 

[Micron]

Ya know, this is a query wizard option for next time - Find unmatched - it would walk you through creating it.
however, I'm assuming you meant that values are in one table and not the other.

 

[Joe4]

Ya know, this is a query wizard option for next time - Find unmatched - it would walk you through creating it.
however, I'm assuming you meant that values are in one table and not the other.

I find that the Unmatched Query Wizard is fine for REAL simple comparisons, where you are only joinging on one table with no critieria, but if you have to join on multiple fields and/or have criteria, it really isn't all that helpful in those cases.

 

[Micron]

IMO, it's a good start if you're not real adept at figuring it out yourself. There is a limit to the number of fields you can pull in with the wizard, but once you have the query basics done, you can add more fields and easily modify or add criteria. To each their own.

 

[Joe4]

IMO, it's a good start if you're not real adept at figuring it out yourself. There is a limit to the number of fields you can pull in with the wizard, but once you have the query basics done, you can add more fields and easily modify or add criteria. To each their own.

Very true, you can use it as a start, the building block of your larger query.
If you did not know what the join needs to look like (which direction the join arrow needs to point in), it would at least show you that, so you know what the other joins you would manually need to add in yourself need to look like.