Combining HAVING and WHERE conditions

[mrb783]

Greetings all,

I have a situation where I created a UNION query to generate a distinct list of items from multiple tables. However, as part of the query, it generated some combinations that have some null fields that I would like to get rid of to clean it up. The problem is, I don't want to get rid of the partial null rows where that is the only row for the ID (see example below).

Example:

ID.....................TEXTFIELD
123...................
ABC..................
ABC..................blah blah blah

Desired Outcome:

ID.....................TEXTFIELD
123...................
ABC..................blah blah blah

I would like to use something similar to the following type of comparison to clean up a query:

SELECT SAMPLE.[ID], SAMPLE.[TEXTFIELD]
FROM SAMPLE
WHERE (((SAMPLE.[TEXTFIELD]) Is Not Null)) OR (((SAMPLE.[TEXTFIELD]) Is Null))
GROUP BY SAMPLE.[ID], SAMPLE.[TEXTFIELD]
HAVING (((Count(SAMPLE.[ID]))>1)) OR (((Count(SAMPLE.[ID]))=1));

The problem is, running this gets rid of all TEXTFIELD = Null rows, not just the ones that I want to get rid of. I also tried this:

SELECT SAMPLE.[ID], SAMPLE.[TEXTFIELD]
FROM SAMPLE
GROUP BY SAMPLE.[ID], SAMPLE.[TEXTFIELD]
HAVING (((SAMPLE.[TEXTFIELD]) Is Not Null) AND ((Count(SAMPLE.[ID]))>1)) OR (((SAMPLE.[TEXTFIELD]) Is Null) AND ((Count(SAMPLE.[ID]))=1));

But then it gets rid of all of the non-Null fields. If I try using a WHERE statement, it doesn't like it. Any ideas?

 

[Banana]

If I am following you, you basically want to show only textvalue but if there is no textvalue for that ID, show the null?

SELECT DISTINCT l.ID, l.TEXTFIELD
FROM SAMPLE AS l
LEFT JOIN (
   SELECT s.ID, s.TEXTFIELD
   FROM SAMPLE AS s
   WHERE s.TEXTFIELD IS NOT NULL
) AS r
  ON l.ID = r.ID;

Does that work?


EDIT:

No, the above SQL is not correct. Let's try again:

SELECT r.ID, r.TEXTFIELD
FROM (
  SELECT ID, COUNT(ID)
  FROM SAMPLE
  WHERE TEXTFIELD IS NULL
  HAVING COUNT(ID)>1
) AS l
INNER JOIN (
  SELECT ID, TEXTFIELD
  FROM SAMPLE
  WHERE TEXTFIELD IS NULL
) AS r
  ON l.ID = r.ID
UNION
SELECT l.ID, l.TEXTFIELD
FROM SAMPLE AS l
WHERE TEXTFIELD IS NOT NULL;

 

[mrb783]

That gives me errors, but I don't think it does what I want. Effectively I want to return the following conditions:

((Count(ID) > 1) AND (TEXTFIELD Is Not Null))
OR
((Count(ID) = 1) AND (TEXTFIELD Is Null))

 

[mrb783]

Oh, and thanks for the prompt initial reply.

 

[Banana]

That gives me errors, but I don't think it does what I want. Effectively I want to return the following conditions:

((Count(ID) > 1) AND (TEXTFIELD Is Not Null))
OR
((Count(ID) = 1) AND (TEXTFIELD Is Null))

Okay - out of curiosity, what was the error? Looking at the SQL again, I think it's because I forgot to include the GROUP BY. Here's the SQL again.

SELECT r.ID, r.TEXTFIELD
FROM (
  SELECT ID, COUNT(ID)
  FROM SAMPLE
  GROUP BY ID
  WHERE TEXTFIELD IS NULL
  HAVING COUNT(ID)=1
) AS l
INNER JOIN (
  SELECT ID, TEXTFIELD
  FROM SAMPLE
  WHERE TEXTFIELD IS NULL
) AS r
  ON l.ID = r.ID
UNION
SELECT l.ID, l.TEXTFIELD
FROM SAMPLE AS l
WHERE TEXTFIELD IS NOT NULL;

As for the condition you mentioned, that can only be evaluated in a HAVING clause since you need to have the results of count to filter upon, so essentially:

SELECT ID, COUNT(ID)
FROM SAMPLE
GROUP BY ID
HAVING ((Count(ID) >= 1) AND (TEXTFIELD IS NOT NULL))
  OR ((Count(ID)=1) AND TEXTFIELD IS NULL))

but I'm not sure that this will work because of the fact that null test is after aggregating and thus too late. That's why I thought UNION query was probably preferable to keep the criteria relatively straightforward. Also, the 2nd composite query does not need to be an aggregating query because "WHERE TEXTFIELD IS NOT NULL" will produce same results as "HAVING (Count(ID)>=1) AND TEXTFIELD IS NOT NULL" but much quicker.

I also have one more uncertainty - could there be rows like this:

ABC | NULL
ABC | NULL

in which the output should be a single row:

ABC | NULL

?

If so, then you may need to change the criteria of COUNT(ID) = 1 to just COUNT(ID)>0 against a WHERE TEXTFIELD IS NULL in the union SQL above.

HTH.