Solved Inner Join Table Query Not Run properly (1 Viewer)

[hrdpgajjar]

Hi all,
I have two tables 1. FarmerT And 2.RTGST

FarmerT has a field "RegNo"
And
RTGST has two fields "RTGSUTR1" And "MailToHO1"

Now i need a data having following criteria,

- No of cases (RegNo) where "MailToHO1" is not null and "RTGSUTR1" is null

this returns as zero records, but actually there are some records.

below is my query code,

SELECT FarmerT.RegNo, RTGST.RTGSUTR1, RTGST.RTGSMailToHO1
FROM FarmerT INNER JOIN RTGST ON FarmerT.FarmerID = RTGST.FarmerID
WHERE (((RTGST.RTGSUTR1) Is Null) AND (Not (RTGST.RTGSMailToHO1) Is Null));

what i am doing wrong?


Thanks

 

[DHookom]

Do you allow zero length strings in your fields? What is returned when you run a similar query without joining to the FarmerT?

Maybe try

SELECT FarmerT.RegNo, RTGST.RTGSUTR1, RTGST.RTGSMailToHO1
FROM FarmerT INNER JOIN RTGST ON FarmerT.FarmerID = RTGST.FarmerID
WHERE RTGST.RTGSUTR1 & "" <> "" AND (Not RTGST.RTGSMailToHO1 Is Null);

 

[The_Doc_Man]

I THINK you have misplaced parentheses. (Not 100% sure.)

WHERE ( ( ( RTGST.RTGSUTR1 ) Is Null ) AND ( Not ( RTGST.RTGSMailToHO1 ) Is Null ) )
WHERE  ( RTGST.RTGSUTR1 Is Null ) AND (Not ( RTGST.RTGSMailToHO1  Is Null ) )

Whatever is in parentheses is evaluated before anything outside the parentheses. But in YOUR expression, you have

( Not (RTGSMailToHO1) Is Null )

which involves a UNARY NOT operator. If that NOT is applied too early, you would have in effect a (NOT null) but because of NULL processing rules, NOT NULL and NULL are BOTH null. By forcing evaluation of the IS NULL before the NOT can get to it, you make the expression unequivocal. The thing I'm not 100% sure about is that the IS operator's precedence is questionable in terms of evaluation granularity. But by fixing the parentheses to force evaluation order, you should have no trouble.

The part that I'm not sure about is the specific order of evaluation when a NULL is involved in a logical operator (NOT) and a comparison operator (IS). To quote one of our more colorful presidents, it all depends on what IS is.

 

[cheekybuddha]

You can also try:

SELECT
  FarmerT.RegNo,
  RTGST.RTGSUTR1,
  RTGST.RTGSMailToHO1
FROM FarmerT
INNER JOIN RTGST
        ON FarmerT.FarmerID = RTGST.FarmerID
WHERE Nz(RTGST.RTGSUTR1, '') = ''
  AND Nz(RTGST.RTGSMailToHO1, '') <> '';

 

[cheekybuddha]

BTW,

Probably better to use:

[Field] IS NOT NULL

rather than

NOT ([Field] IS NULL)

If anything, it's less confusing visually

 

[plog]

1. Non-null "Nulls". Your eyes cannot be trusted to see a null value. Just because it looks like there is no data in a field, doesn't mean it is null. It could be an empty string (""). It could be a weird character (line return, tab, something else). To a computer all those things I just mentioned are not technically null. Just because it looks "null" doesn't mean it is actually null.

2. No matches between tables. If a record is in RTGST and meets the criteria that doesn't mean it will show up in the final result. This is because you use an INNER JOIN which itself acts as criteria. That criteria means it must have a matching FarmerID in FarmerT. So, if a record in RTGST has a RTGSMailToHO1 value and a null value in RTGSUTR1, it would not make the query results if its FarmerID doesn't match any FarmerID in the FarmerT table.

 

[MadPiet]

Hi all,
I have two tables 1. FarmerT And 2.RTGST

FarmerT has a field "RegNo"
And
RTGST has two fields "RTGSUTR1" And "MailToHO1"

Now i need a data having following criteria,

- No of cases (RegNo) where "MailToHO1" is not null and "RTGSUTR1" is null

this returns as zero records, but actually there are some records.

below is my query code,

SELECT FarmerT.RegNo, RTGST.RTGSUTR1, RTGST.RTGSMailToHO1
FROM FarmerT INNER JOIN RTGST ON FarmerT.FarmerID = RTGST.FarmerID
WHERE (((RTGST.RTGSUTR1) Is Null) AND (Not (RTGST.RTGSMailToHO1) Is Null));

what i am doing wrong?


Thanks

What IS this?
WHERE (((RTGST.RTGSUTR1) Is Null) AND (Not (RTGST.RTGSMailToHO1) Is Null));???

Why make simple things so difficult? One: remove all the parentheses. You don't need ANY of them.

WHERE RTGST.RTGSUTR1 IS NULL AND RTGSTT.MailToHO1 IS NOT NULL

yup. super hard.

 

[hrdpgajjar]

Do you allow zero length strings in your fields? What is returned when you run a similar query without joining to the FarmerT?

Maybe try

SELECT FarmerT.RegNo, RTGST.RTGSUTR1, RTGST.RTGSMailToHO1
FROM FarmerT INNER JOIN RTGST ON FarmerT.FarmerID = RTGST.FarmerID
WHERE RTGST.RTGSUTR1 & "" <> "" AND (Not RTGST.RTGSMailToHO1 Is Null);

without joining FarmerT it works flowless, but when i join them it becomes mess

 

[hrdpgajjar]

Do you allow zero length strings in your fields? What is returned when you run a similar query without joining to the FarmerT?

Maybe try

SELECT FarmerT.RegNo, RTGST.RTGSUTR1, RTGST.RTGSMailToHO1
FROM FarmerT INNER JOIN RTGST ON FarmerT.FarmerID = RTGST.FarmerID
WHERE RTGST.RTGSUTR1 & "" <> "" AND (Not RTGST.RTGSMailToHO1 Is Null);

This gives result with both field has value, i need RTGSMAIL field with no value

 

[hrdpgajjar]

You can also try:

SELECT
  FarmerT.RegNo,
  RTGST.RTGSUTR1,
  RTGST.RTGSMailToHO1
FROM FarmerT
INNER JOIN RTGST
        ON FarmerT.FarmerID = RTGST.FarmerID
WHERE Nz(RTGST.RTGSUTR1, '') = ''
  AND Nz(RTGST.RTGSMailToHO1, '') <> '';

This works as i want, thanks

 

[cheekybuddha]

This works as i want, thanks

NOTE: The solution I provided is sub-optimal.

Unless it is essential to allow empty string values in fields RTGSUTR1 and RTGSMailToHO1 (very rare) then it would be better to convert all empty strings to NULL and prevent empty values.

Then your queries on those fields can use indexes (if they are indexed, which they should be if they are used as criteria).

(Make a backup of your db first!)

You can run the following query to convert all empty string values in those fields to NULL:

UPDATE RTGST
SET
  RTGSUTR1 = IIf(RTGSUTR1 = '', NULL, RTGSUTR1),
  RTGSMailToHO1 = IIf(RTGSMailToHO1 = '', NULL, RTGSMailToHO1)
;

Then, with table RTGST in design view, change the following properties for those fields:
'Allow Zero Length' = 'No'
'Indexed' = 'Yes (Duplicates OK)'

Then your original query can just become:

SELECT
  FarmerT.RegNo,
  RTGST.RTGSUTR1,
  RTGST.RTGSMailToHO1
FROM FarmerT
INNER JOIN RTGST
        ON FarmerT.FarmerID = RTGST.FarmerID
WHERE RTGST.RTGSUTR1 IS NULL
  AND RTGST.RTGSMailToHO1 IS NOT NULL;

 

[Pat Hartman]

I agree, ZLS should almost never be allowed and MS should make the default null for text fields rather than ZLS. As it is, I have to remember to ALWAYS change the default for every single text field I create. I also have code that does it after the fact. One of my frequent requests for changes to Access is for MS to allow ME to specify the default value for each type of field so that I don't have to remember to change the default for almost every field I create. MS also got the default wrong for long integers. Most long integers are FKs and they should NEVER, EVER default to zero. They should always default to null. So, my code fixes those also.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk