LEFT JOIN?

[theunknownknight]

I am writing an ASP.NET app using Access 2000

I am doing a left join between 2 tables.
I know that it will return everything on the left as well as everything that it matches with on the right.

HOWEVER, I want to do a left join that returns everything on the left BUT leave out all the records that match with the right.

How would I do this?

 

[RealQuiet]

Try adding a where clause:

WHERE RightTable.Field IS NULL

This should return you only the records where a field in the left table doesn't match a field in the right.

 

[Zarcom]

Not quite RQ. Unless I am mistaken that will return all records on the left and all records on the right that have Null or nothing entered in said field.

The proper syntax should be
WHERE RightTable.Field <> LeftTable.Field That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[RealQuiet]

It is true that you will have to pick a field (such as a PK) that is guaranteed not to be null.
If you do a left join, you'll see that all the fields are null for the selected fields from the right table that don't match the condition. This is how you tell that there are no matching records from the right table.
Adding a where to specify that it is null will make sure you ONLY get the left table records where there is no match in the right table.

Zarcom, using the RightTable.Field <> LeftTable.Field would return many, many right table records for each left table record since that inequality condition would be true for possibly all of the records in the right table.

 

[Zarcom]

My bad RQ I didn't verify anything before I posted That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540