Query Problem

[Gramps]

Hi All!,

I have a question regarding SQL Queries and nulls.

This is my scenario.

I am comparing two tables, tblImportAuditTable and tblDevices. There are 5 fields that are being compared to see if they are the same. The primary key in tblDevices is the Serial Number. I want the query to operate such that if any of the 5 fields that are being compared are not the same, then I want those records returned. The problem I am having is that the AssetTag field in either table can be null in some circumstances. When this is the case, the query returns the record where the AssetTag is null. I DON'T want that to happen. I want it so that if the Query sees the AssetTag field in both tblImportAuditTable and tblDevices as null, I want it to see that as a match and not return the record. At the moment, the opposite happens.

The query is below. Any suggestions? Thanks in advance!

SELECT tblImportAuditTable.Device, tblImportAuditTable.AssetTag, tblImportAuditTable.Serial, tblImportAuditTable.StoreLocation, tblImportAuditTable.Status
FROM tblImportAuditTable LEFT JOIN tblDevices ON (tblImportAuditTable.Status = tblDevices.Status) AND (tblImportAuditTable.StoreLocation = tblDevices.StoreLocation) AND (tblImportAuditTable.AssetTag = tblDevices.AssetTag) AND (tblImportAuditTable.Device = tblDevices.Device) AND (tblImportAuditTable.Serial = tblDevices.Serial)
WHERE (((tblDevices.Serial) Is Null));

 

[plog]

I want the query to operate such that if any of the 5 fields that are being compared are not the same, then I want those records returned

That explanation doesn't jive with your SQL in the least. In fact, that explanation can't be accurate. Let me explain with data:

T1
F1, F2, F3
Sally, 48, 5/1/2012
Larry, 4, 4/1/2010

T2
F1, F2, F3
Sally, 48, 5/1/2012
Larry, 4, 4/1/2011

Notice that they are essentially the same set of data except for that last date. Now, going off the explanation you provided this is the data that you would want returned:

T1.F1, T1.F2, T1.F3, T2.F2, T2.F3
Sally, 48, 5/1/2012, Larry, 4, 4/1/2011
Larry, 4, 4/1/2010, Sally, 48, 5/1/2012
Larry, 4, 4/1/2010, Larry, 4, 4/1/2011

I matched every record in the 1st table against every record in the 2nd table. Now out of a 4 total permuations (2 x 2) of records, only 1 matched exactly (Sally in both tables), meaning that 3 didn't match and should be returned. Your resulting data is going to increase geometrically with the more records you have in each table (3 records x 3 records = 9 permutations). If you copied a table with 4 records and then ran your process your query would return 12 results (4 records x 4 records - 4 exact match records).

That can't be what you want. Please restate what you want, or provide sample data demonstrating it.

 

[Gramps]

Hi plog,

Thanks so much for taking the time to look at my problem.

To be honest, I am struggling with understanding SQL, and I think it shows.

The database in question is to manage stock of "Store Rooms" of computer equipment. Like any store room, it needs to be audited from time to time. It is this audit function I am struggling with.

The table tblDevices contains the store room data.

The table tblImportAuditTable contains the information gathered from the audit.

What I want to do is compare the audited data (tblImportAuditData) with what is recorded in the database (tblDevices), using the Serial field in tblDevices to locate the record in tblImportAuditData. If any of the fields in tblImportAuditData do not exactly match the corresponding fields in tblDevices, I want those record(s) from tblImportAuditData returned by the query.

I have attached a cut down copy of the database with just the two tables and the query as I posted originally. I have included some dummy data & the data in both tables is identical. When the query runs, it returns the two records which have no asset tag. I don't want it to do that. If the asset tag is blank in both tblDevices and tblImportAuditTable, then I don't what those records returned because as far as I am concerned, they match. They are both blank.

If tblDevices had a record that had a blank AssetTag field and tblImportAuditTable had that same record with an AssetTag specified, I would expect that to return the record, but not when they are both blank.

I hope this makes sense.

 

[plog]

So the query should return no data because the Asset tag data is null? In that case you simply put criteria under the AssetTag field to exclude those that are null:

Is Not Null

 

[Gramps]

Hi plog,

I almost can't believe it was that simple.

I'm very embarrassed.

Thanks for your help.

I am going to go away now and dig a nice big hole. Can you fill it in for me after I hop in?

Thanks.

 

[plog]

I don't believe its that easy. I'm not convinced that query does what you want. Its easy to write a query that returns no data at all.

What you need to do is insert sample data into your tables so that should be returned by the query. Then run the query and see if it returns it.

 

[Gramps]

Hi plog,

It seems to be doing what I need so far. Just doing more tests to confirm, but so far so good!