Queries - SQL

[PatAccess]

Hello All,
I am trying to create a query with 2 queries by writing my own SQL but it not giving me the correct return set. The RS should be record where 2 fields math but 2 others do not.
Here is my code:

SELECT [Qry_EngineerLic-PE].EmpID, [Qry_EngineerLic-PE].FName,  [Qry_EngineerLic-PE].StateID
FROM [Qry_EngineerLic-PE] , Qry_FromVision_PELicenses
WHERE  [Qry_EngineerLic-PE].EmpID = Qry_FromVision_PELicenses.Employee AND  [Qry_EngineerLic-PE].StateID<>Qry_FromVision_PELicenses.StateRegistered;

What am I doing wrong here?

 

[theDBguy]

Are you getting any errors?

 

[Gasman]

I would put ( ) around your conditions to be explicit.
I generally use the GUI and then modify as my SQL is not great.

 

[PatAccess]

Are you getting any errors?

No, the result set is over 2000 records which is not right

 

[Gasman]

No join?

 

[theDBguy]

No, the result set is over 2000 records which is not right

Probably becase, as Gasman was trying to say, you're getting a Cartesian Product.

 

[Isaac]

If Access was anything like ansi SQL, I think a particularly constructed Where clause can yield the same results as an inner join.
But I have never tried doing that in Access! Can you post a copy of your DB?

 

[plog]

No, the result set is over 2000 records which is not right

Define not right? Too many? Too few?

In either case, you want to find an incorrect record and find out why it is/isn't appearing and adjust your SQL so that it fixes it. Most likely it will fix other records as well. If it doesn't fix all of them, find another record and continue adjusting your SQL.

Also, are the queries you built this query on correct? Do they have more/less records in them than you expect?

Additionally you should use an INNER JOIN when fields in both tables are to be equal and not put that in the WHERE.

 

[PatAccess]

Define not right? Too many? Too few?

In either case, you want to find an incorrect record and find out why it is/isn't appearing and adjust your SQL so that it fixes it. Most likely it will fix other records as well. If it doesn't fix all of them, find another record and continue adjusting your SQL.

Also, are the queries you built this query on correct? Do they have more/less records in them than you expect?

Additionally you should use an INNER JOIN when fields in both tables are to be equal and not put that in the WHERE.

I found that it will work if I specify 2 sets of fields to be equal and not one, which is weird. I am working on it with all of your suggestions. Thank you

 

[Isaac]

I found that it will work if I specify 2 sets of fields to be equal and not one, which is weird. I am working on it with all of your suggestions. Thank you

Hmm, sounds like you just needed more Where clause filtering. Maybe this further illustrates the "readability" of using a "on" style join, rather than a "comma" join with what would be join predicates, in the Where clause.
This way, you separate in your mental logic / process, into two separate tasks:
- how to properly formulate your JOIN
- what additional filters are needed to simply "filter" data

Though it might be possible to merge the concepts by putting them all in the Where clause, most people find it more helpful to separate them.

 

[Pat Hartman]

If one of the fields in a comparison might be null, using <> will not give you the results you expect.

If a <> b -- result = true
if a <> null -- result = false since null compared to anything is always false.

 

[cheekybuddha]

Do you get the same results if you use:

SELECT 
  e.EmpID, 
  e.FName, 
  e.StateID
FROM [Qry_EngineerLic-PE] e 
INNER JOIN Qry_FromVision_PELicenses l
        ON e.EmpID = l.Employee
WHERE e.StateID <> l.StateRegistered;