Sql Intersect

[Radu84]

Hi,

I am trying to use the sql Intersect keyword in ms access 2000 but it doesnt seem to work. Anybody know of any other way to do this ?

 

[pdx_man]

Set Theory.

From SQL BOL:

Intersect
Returns the intersection of two input sets, optionally retaining duplicates.

Syntax
Intersect(«Set1», «Set2»[, ALL])

Remarks
This function returns the intersection of «Set1» and «Set2». By default, duplicates are eliminated from both sets prior to intersection.

The optional ALL retains duplicates. There are several ways for ALL to work. The algorithm is: Nonduplicated elements are intersected as usual. For each duplicate in «Set1», match it with a duplicate in «Set2», if one exists, and keep matching duplicates in the intersected set.

Example
This example

Intersect({[1994], [1995], [1996]}, {[1995], [1996], [1997]})

returns the set {[1995], [1996]}.
----------------------------------------------------------------------


So, if you are using tables, an inner join will do the same thing (same as the ALL) option above. If you want want distinct values, then use the DISTINCT keyword.

USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

 

[alirezamx]

I have an intersect query below

---------

SELECT distinct food.rating, food.foodname, food.foodid, food.pic_path, requirement.Req_Name, requirement.Req_Nik
FROM requirement INNER JOIN (food INNER JOIN req_food ON food.foodid = req_food.food_id) ON requirement.Req_Id = req_food.req_id
WHERE (((requirement.Req_Name) Like "N'%...%'") AND ((requirement.Req_Nik) Like "N'%...%'")) intersect SELECT distinct food.rating, food.foodname, food.foodid, food.pic_path FROM cat_food INNER JOIN food ON cat_food.food_id = food.foodid where ((foodname like "N'%...%'") AND ( Rating=1 OR Rating=2 OR Rating=3 OR Rating=4 OR Rating=5 ))

--------

What is the equivalent in Access?
thank you.

 

[Rabbie]

Look at the end of post#3

So, if you are using tables, an inner join will do the same thing (same as the ALL) option above. If you want want distinct values, then use the DISTINCT keyword.