Why Doesn't This SQL Code Work?

Question

SELECT [Name3] FROM [DataTable3]
INTERSECT
SELECT [Name4] FROM [DataTable4];

I am trying to intersect these two columns in MS-Acess and it is not working. It keeps giving me this error: SYNTAX ERROR IN FROM CLAUSE.

Could it be that INTERSECT is a command for SQL Server only?

I have even tried this code and it's not working:

SELECT [DataTable3].Name3 FROM [DataTable3]
INTERSECT
SELECT [DataTable4].Name4 FROM [DataTable4];




Help!!!!!!!!! Thanks a lot.

Great Answers!!! Thank you all!!!

Answer 1

I believe you will want to use the 'Union' command there.

SELECT [DataTable3].Name3 FROM [DataTable3]
UNION
SELECT [DataTable4].Name4 FROM [DataTable4]

"Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements)." - from http://www.1keydata.com/sql/sql-intersect.html

I could be wrong, I guess it depends on what you are trying to do. Good luck!

Answer 2

Yes, INTERSECT is not a command that Access will recognize. What you need to to is an INNER JOIN, specifying the columns on which you want to match. For example:

SELECT [DataTable3].Name3, [DataTable4].Name4
FROM DataTable3 Inner Join DataTable4
ON DataTable3.EmployeeID = DataTable4.EmployeeID;

Answer 3

Actually it only can be executed in MSSQL 2005, SQL 2000 can not accept it.
You need to use inner join like:

SELECT [DataTable3].Name3
FROM [DataTable3] inner join [DataTable4]
On [DataTable3].Name3 = [DataTable4].Name4;


UNION will make all Names showing for [DataTable3] and [DataTable4]

Answer 4

Without a timestamp of similar, how are you measuring recent active users? Without using some sort of changing column value, the most active users will typically always be listed are the same 3. Relatively. You need to have a timestamp of their last activity, or something to sort the list by. SELECT username FROM users ORDER BY timestamp DESC LIMIT 0,3 Cheers, Gitlez

Answer 5

I use WHERE... AND...

but Jet Engine of MS Access has no INTERSECT to use.

Answer 6

no intersect use the UNION keyword