How can I force a NULL to return in a simple query?

[RileyCat]

I need a way to force a NULL value to return in a simple query instead of just an empty result set if the "where" clause does not yield any results.

Example . . .

Table A Table B
NameColumn char (15) StreetColumn char (20)
NumberColumn int NumberColumn int
IdColumn int AcctColumn int

Table "A" and table "B" are joined on "NumberColumn".

My query looks like this .......

select   NameColumn
from     TableA MyA
         inner join TableB MyB on
              MyB.AcctColumn = 123456
where    MyA.NumberColumn = MyB.NumberColumn

The problem is, if TableB does not contain a record for AcctColumn 123456 then I am just getting back an empty result set. I actually want a NULL return so I can default the return to the caller something like "Not Found" without having to look at the row count.

I am doing this in batch to a report so it's not inline or on request. I could have many rows in Table "A" without a matching row in Table "B". This data is going to a report so I need some way to tell the user of the report that the corresponding data is not found in table "B".

Ideas? Suggestions?

Thanks.


Stay Cool Ya'll!

-- Kristin

 

[bborissov]

select   NameColumn
from     TableA MyA
         inner join TableB MyB on
              MyB.AcctColumn = 123456
where    MyA.NumberColumn = MyB.NumberColumn
IF @@ROWCOUNT = 0
   SELECT NULL

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

what you want is a LEFT OUTER JOIN

SELECT MyA.NameColumn
  FROM TableA AS MyA
[red]LEFT OUTER[/red]
  JOIN TableB AS MyB 
    [red]ON[/red] MyB.NumberColumn = MyA.NumberColumn 
   AND MyB.AcctColumn = 123456

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

That would not return a NULL. It will retyurns ALL records from TableA.

BTW I just saw the syntax used in the query:

SELECT NameColumn
       FROM TableA MyA
INNER JOIN TableB MyB
      ON  MyA.NumberColumn = MyB.NumberColumn AND
          MyB.AcctColumn   = 123456

IF @@ROWCOUNT = 0
   SELECT NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.