Solved SQL/Query <> not working

[PatAccess]

Hello guys,
I noticed that "<>" has not been working for several of my queries in the query design view and the SQL View. Is there a reason why?
I have 2 queries and I want records where the 2 states are not equal but it is returning all the states from one of the query
Here is the SQL View code

SELECT Qry_States.State
FROM QryBobRegistration, Qry_States
WHERE(((Qry_States.State)<>[QryBobRegistration].[StateRegistered]));

In the Design View, I have: <>[QryBobRegistration].[StateRegistered] on the Criteria Row.

What am I missing?

 

[jdraw]

Can you post the sql for the queries?

Can you provide some samples of this? Or a plain English overview of your requirement.

I want records where the 2 states are not equal

 

[Cronk]

What am I missing?

A closing parenthesis?

WHERE(((Qry_States.State)<>[QryBobRegistration].[StateRegistered])));

 

[theDBguy]

Did that query use to work before? As written, I don't know if it ever did. If you had two tables and both had CA and NV on them, for example, your query would return both states because CA is not equal to NV and NV is not equal to CA.

 

[plog]

You have 2 data sources in your FROM which results in a Cartesian product. That means every record in one data source will match with every record in the other data source. You need to JOIN them somehow. Most likely a LEFT JOIN.

Perhaps you can give us sample data from both the datasources and then what data you expect the query to return when you feed in that data.

 

[Minty]

Left Join the two queries on State and select IS Null in the criteria on one side?

 

[Isaac]

Yeah don't you need some kind of join??

 

[PatAccess]

Can you post the sql for the queries?

Can you provide some samples of this? Or a plain English overview of your requirement.

I just thought about something. I am basically looking to return an unmatched query so I think plog is right because I used Join and it is working now. BUT I would still like to understand why "<>" seems not to work in this case?
If I say give me records from QryA where QryA.field <> QryB.field, why would it not return anything, or all values in QryA?

 

[PatAccess]

You have 2 data sources in your FROM which results in a Cartesian product. That means every record in one data source will match with every record in the other data source. You need to JOIN them somehow. Most likely a LEFT JOIN.

Perhaps you can give us sample data from both the datasources and then what data you expect the query to return when you feed in that data.

The LEFT JOIN worked. Thank you. I just saw that it was basically an unmatched query but I am still not understanding why "<>" never works

 

[PatAccess]

Yeah don't you need some kind of join??

Yes, I tried JOIN and it worked. Thank you

 

[plog]

Table1
[F1]
A
B
C

Table2
[F2]
A
B


SELECT [F1], [F2] FROM Table1, Table2

This is a simple Cartesian Product, it matches every value in Table1 with every value in Table2 because there is no relationship. These are the results of that query:

[F1], [F2]
A, A
A, B
B, A
B, B
C, A
C, B


Now we add in your criteria in the form of the WHERE:

SELECT [F1], [F2], FROM Table1, Table2 WHERE [F1]<>[F2]

[F1], [F2]
A, B
B, A
C, A
C, B

It eliminates the matched pairs of values. Every value in Table1 still remains as does every value of Table2 beause they have non-matching data in the other table. That's how your query was working.

 

[theDBguy]

Hi. In your original query, you asked to return only the state from one table. In @plog's example, the states from both tables are returned. But in both occasions, you'll see that all the states from each table are returned because of what I said earlier. A<>B<>C<>B<>A.

So, it doesn't matter really whether you use a JOIN or not, the <> wasn't what you wanted to use.

In other words, neither if these will work.

select * from a, b where a<>b

or

select * from a join b on a<>b

 

[PatAccess]

Table1
[F1]
A
B
C

Table2
[F2]
A
B


SELECT [F1], [F2] FROM Table1, Table2

This is a simple Cartesian Product, it matches every value in Table1 with every value in Table2 because there is no relationship. These are the results of that query:

[F1], [F2]
A, A
A, B
B, A
B, B
C, A
C, B


Now we add in your criteria in the form of the WHERE:

SELECT [F1], [F2], FROM Table1, Table2 WHERE [F1]<>[F2]

[F1], [F2]
A, B
B, A
C, A
C, B

It eliminates the matched pairs of values. Every value in Table1 still remains as does every value of Table2 beause they have non-matching data in the other table. That's how your query was working.

Makes sense. Thank you for your help