Please help totally lost on syntax

MeterMan

I have two tables that I have joined First table is ExpenseReport and has a field in it called tboxBeginDate Second table is called BeginDates and has a field in it also called tboxBeginDate. I want to query and show only those dates in BeginDates that are not used in ExpensReport Select ExpenseReport.tboxBeginDate, BeginDates.tboxBeginDate from BeginDates.tboxBeginDate RIGHT JOIN ON ExpenseReport.tboxBeginDate where BeginDats.tboxBeginDate != ExpenseReport.tboxBeginDate. any help would be great thanks Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"

Michael Potter

Either of these should work. I prefer the first one for maintainability. If your tables are large the second one may be quicker with the correct indexes:

SELECT 
    tboxBeginDate
FROM
    BeginDates
WHERE
    tboxBeginDate NOT IN (SELECT DISTINCT
                              tboxBeginDate
                          FROM
                              ExpenseReport)

OR

SELECT
    bd.tboxBeginDate
FROM 
    BeginDate bd
LEFT JOIN
    ExpenseReport er
    ON (bd.tboxBeginDate = er.tboxBeginDate)
WHERE
    er.tboxBeginDate IS NULL

MeterMan

thanks a bunch you wouldn't happen to have a good place "tutorial" that will help you with sql other than w3schools would you? thanks again Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"

MeterMan

When I try both of your methods it ask me to enter a value for bd.tboxBeginDate I checked the database and this table has values so I'm not sure whats wrong must be something with the join statement :( Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"

Michael Potter

I don't know any really good sites. You may want to look at http://www.sqlservercentral.com[^]. Sign up for their newsletter and you will get a little dose of knowledge every day. The book I learned the most from was written by Ken Henderson: The Guru's Guide to Transact-SQL. It was tough to grasp at first but, it really gives you confidence with the SQL language.

Michael Potter

I am guessing you are not using SQL server. Are you using Access?

MeterMan

Yeah unfortunately I'm using access for now. We will be converting to sql server soon but for now Access. Sorry I should have been more specific. Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"

Michael Potter

Access syntax is very different from SQL. The join syntax is really nasty when mulitple joins are involved. I used to write code that converted between the two but, that was many years ago. I think you can modify your original WHERE clause like this:

SELECT
    ExpenseReport.tboxBeginDate, 
    BeginDates.tboxBeginDate 
FROM
    BeginDates.tboxBeginDate 
LEFT JOIN ON 
    ExpenseReport.tboxBeginDate 
WHERE
    BeginDates.tboxBeginDate == ExpenseReport.tboxBeginDate AND
    ExpenseReport.tboxBeginDate IS NULL

The 'IS NULL' is what I am not sure about. Create a new query and paste this into the SQL view of the query and see if it resolves correctly by going back to the design view. I can't remember how null is resolved in Access. It might be ISNULL([Field Name]) or something like that. Do remember the following, when NULL is directly compared with a defined value: BeginDats.tboxBeginDate != ExpenseReport.tboxBeginDate Your result will be NULL not TRUE as you expected. That is why you would never get the correct result. -- modified at 14:48 Friday 24th March, 2006

MeterMan

Thanks for all your help. I was wondering about that != but yeah makes scense kinda like multiplying something by 0 it is always goning to be zero. again thanks Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"