INTERSECT command

[JoaoAfonso]

Good evening.
I am trying to make an intersection between 2 selects, but things are not working as supposed. If I use them individually binding the result variable, both return what's expected, but using INTERSECT, my program do not get any result...

'first_table' has the following columns: 'number','activity_name','person','phone'. 'second_table' has the following columns: 'activity_number','initial_date','final_date'.
'number' and 'activity_number' from 'first table' and 'second table', respectively, are the numbers I want to check.
Using this, wont return anything:

Code Select Expand

"SELECT number FROM first_table WHERE activity_name='physical training' AND person='male' INTERSECT SELECT activity_number FROM second_table WHERE initial_date='01JAN08'

Maybe this can be important, but both number and activity_number columns are of the same type.

[LarryMc]

Do you get an error message of any kind?

Do each of the SELECT statements return expected results when used by themselves?

Larry

[LarryMc]

Found this on the internet in some SQL documentation:

In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they both return the same number of columns, and that the corresponding columns have compatible data types

and

Intersection and Set-Difference
The INTERSECT and EXCEPT operators of SQL are not supported in mySQL. We suggest that instead, you use a join with all corresponding attributes equated in place of an intersection. For instance, to get the intersection of R(a,b) and S(a,b), write:

SELECT DISTINCT *
FROM R
WHERE EXISTS (SELECT * FROM S WHERE R.a = S.a AND R.b = S.b);

Larry

[JoaoAfonso]

This is a translation from CB and in CB this worked with the same columns... in CB I used SQLite 3.

Also, both selects work as expected alone.

I know I can work around... but using more code.

Thanks, I will try that DISTINCT keycode.