Select Query of Two Joined Tables With Field Criteria "Is Null"

[TOTCOM11]

I have two tables in a query. One that is called tblProducts and the other that is called tblPictureIndex. I have the relationships set up so that I am including ALL records from tblProducts and only those records from tblPictureIndex where the joined fields are equal. The common field between these tables is the Primary Key named "ID" (without the quotes). What I am trying to do is run a query that brings up records in which the field "Picture1" in the tblPictureIndex table is null. This field is an integer (not Long Integer) and the default value is set to null. When I tried typing "Is Null" into the criteria for this query, it did not change any of the output. What am I doing wrong?

 

[r937]

the problem here is that you are getting unmatched rows, because what you have is a left outer join, and any rows of the tblProducts table which don't have matching rows in the tblPictureIndex table will be in the result set, and the Picture1 field in all of those unmatched rows will by null

you can either change the query to an inner join --

select 
  from tblProducts
inner
  join tblPictureIndex
    on tblProducts.id
     = tblPictureIndex.id   
 where tblPictureIndex.Picture1 is null

or you can leave it as a left outer join but make sure there's a matched row --

select 
  from tblProducts
left outer
  join tblPictureIndex
    on tblProducts.id
     = tblPictureIndex.id   
 where tblPictureIndex.id is not null
   and tblPictureIndex.Picture1 is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

I'm not one who really writes many queries in SQL. Is there a way you could explain this to me using the design view method for queries?

 

[r937]

well, no, i can't, but if you open up a new query, then select View > SQL View, then paste the above query, then select Design View, it should be clear there

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

The reason I'm asking is because this is not the only part of my query. I have many other fields that aren't worth listing. I could copy the SQL of what I currently have and have you look at it. Here it is: How should I modify this?

SELECT tblPictureIndex.PICTURE1, tblPictureIndex.PICTURE2, tblPictureIndex.PICTURE3, tblProducts.PrimalGroup, tblProducts.ProductCode, tblProducts.Grade, tblProducts.ProductDescription
FROM tblProducts LEFT JOIN tblPictureIndex ON tblProducts.ID = tblPictureIndex.ID
WHERE (((tblProducts.Grade)="Choice") AND ((tblProducts.Status)="Active") AND ((Right([ProductCode],2)) Not In ('CA','NA','AN','CX')) AND ((Mid([ProductCode],6,1)) Not In ('4','5'))) OR (((tblProducts.Grade)="Choice or Higher") AND ((tblProducts.Status)="Active") AND ((Right([ProductCode],2)) Not In ('CA','NA','AN','CX')) AND ((Mid([ProductCode],6,1)) Not In ('4','5')))
ORDER BY tblProducts.PrimalGroup, tblProducts.ProductCode, tblProducts.Status;

Let me know! Thanks for your help!

 

[r937]

select pi.PICTURE1
     , pi.PICTURE2
     , pi.PICTURE3
     , p.PrimalGroup
     , p.ProductCode
     , p.Grade
     , p.ProductDescription
  from tblProducts as p
left outer
  join tblPictureIndex as pi
    on p.ID 
     = pi.ID
 where p.Grade 
       in ('Choice','Choice or Higher')
   and p.Status = 'Active' 
   and Right(p.ProductCode,2) 
       Not In ('CA','NA','AN','CX')
   and Mid(p.ProductCode,6,1) 
       Not In ('4','5')
   and pi.ID is not null
   and (
       PICTURE1 is null
    or PICTURE2 is null
    or PICTURE3 is null
       )
order 
    by p.PrimalGroup
     , p.ProductCode
     , p.Status

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

That didin't work. The query does not show ONLY those records which do not have numbers in PICTURE1 field.

 

[r937]

okay, but you get the idea, right?

instead of

   and (
       PICTURE1 is null
    or PICTURE2 is null
    or PICTURE3 is null
       )

you would just use

   and PICTURE1 is null

or are you suggesting that there could be alpha characters in there instead? because you said "records which do not have numbers in PICTURE1 field"


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

No, I just used the Picture1 is null part and it didn't work. There are no alpha characters in field "PICTURE1". This field is strictly a numeric Integer.

 

[r937]

what does "it didn't work" mean?

did you get an error?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

It didn't work means I did not get an error, but when I open the query there is not data to be displayed at all. When I add the lines that you had Picture2, Picture3, etc... nothing changes from what I had before.

 

[TOTCOM11]

This is my current code:

SELECT pi.PICTURE1, pi.PICTURE2, pi.PICTURE3, p.PrimalGroup, p.ProductCode, p.Grade, p.ProductDescription
FROM tblProducts AS p LEFT JOIN tblPictureIndex AS pi ON p.ID = pi.ID
WHERE (((pi.PICTURE1) Is Null) AND ((p.Grade) In ('Choice','Choice or Higher')) AND ((p.Status)='Active') AND ((Right([p].[ProductCode],2)) Not In ('CA','NA','AN','CX')) AND ((Mid([p].[ProductCode],6,1)) Not In ('4','5')) AND ((pi.ID) Is Not Null))
ORDER BY p.PrimalGroup, p.ProductCode, p.Status;

 

[r937]

that means there aren't any rows that satisfy the conditions

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

that's not true though...because looking at it manually (without any filters) there are plenty of records that do not have anything in Picture1.

 

[r937]

yes, but do they meet all the other conditions?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

Yes, they meet all other conditions. I have checked and double checked this. Your code isn't working for me for some reason.

 

[r937]

try inner join instead of left outer join

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TOTCOM11]

Do I do that just by changing "Left Join" to "Inner"?