Help with multi-row query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lars - 18 Jul 2008 21:31 GMT

I have a table that looks like this:

CREATE TABLE TBL(
    ID int IDENTITY(1,1) NOT NULL,
    ForeignKeyID int NOT NULL,
    BatchID int NULL,
    DatePrinted smalldatetime NULL
)

I want to do something like:

    SELECT ID, ForeignKey, BatchID, DatePrinted
    FROM TBL
    WHERE ForeignKey=123

This query can return 0 to many rows. But what I really want to return
from the query is whether any of the returned rows has BatchID as
NULL. If one or more rows has BatchID as NULL, I want to return TRUE,
otherwise FALSE.

Thanks, Lars

Reply to this Message

Alex Kuznetsov - 18 Jul 2008 21:41 GMT

> I have a table that looks like this:
>
[quoted text clipped - 17 lines]
>
> Thanks, Lars

SELECT COUNT(*)
FROM TBL
WHERE ForeignKey=123 AND BatchID IS NULL

Reply to this Message

Russell Fields - 18 Jul 2008 21:50 GMT

And, just for fun, if you want only a 1 or a 0 from the COUNT(*):

SELECT SIGN(COUNT(*))
FROM TBL
WHERE ForeignKey=123 AND BatchID IS NULL

RLF

>> I have a table that looks like this:
>>
[quoted text clipped - 21 lines]
> FROM TBL
> WHERE ForeignKey=123 AND BatchID IS NULL

Reply to this Message

Russell Fields - 18 Jul 2008 21:47 GMT

If I understand you, you don't want the details rows, just the state of TRUE
or FALSE. If that is correct then:

IF EXISTS (SELECT * FROM TBL WHERE ForeignKey=123 and BatchID IS NULL)
SELECT 'TRUE'
ELSE
SELECT 'FALSE'

RLF

>I have a table that looks like this:
>
[quoted text clipped - 17 lines]
>
> Thanks, Lars

Reply to this Message

Erland Sommarskog - 18 Jul 2008 23:17 GMT

> I have a table that looks like this:
>
[quoted text clipped - 15 lines]
> NULL. If one or more rows has BatchID as NULL, I want to return TRUE,
> otherwise FALSE.

SELECT CASE WHEN EXISTS (SELCT *
FROM TBL
WHERE ForeignKey = 123
AND BatchID IS NOT NULL)
THEN 1
ELSE 0
END

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message