How Do I Return True or False on query... 1

[DTJeff]

Hi.

I've got a little head scratcher here which i'm hoping someone can help with.

I have a table with several fields in it which cater for 60'000 records or so. The table is like this:

ID Field1 Field2
----------------------------
0 'SomeText' 'SomeText'
1 '' 'SomeText'
etc

I want to call a query that returns the ID number and then a 1 or 0 (or true or false) if the column contains data.
e.g.:

ID Field1 Field2
----------------------------
0 1 1
1 0 1

or

ID Field1 Field2
----------------------------
0 True True
1 False True

I will probably also want to filter this as well to exclude text phrases such as 'none' or 'n/a' etc.

To add to this I'd also like to return the data from several tables (which are all referenced by the same id in the same way) at the same time, but rather suspect this will be too much?

e.g:
ID Tbl1.Field1 Tbl1.Field2 Tbl2.Fieldx Tbl3.Fieldx
--------------------------------------------------------
0 True True False True
1 False True False False


Any ideas?

Thanks.

Jeff

 

[bborissov]

-- If the fields are char, varchar or nvarchar
SELECT Id,
       MAX(CASE WHEN LEN(LTRIM(Tbl1.Field1)) = 0 OR
                     Tbl1.Field1 IS NULL
                THEN 0
                ELSE 1 END) AS Tbl1_Fld1,
       MAX(CASE WHEN LEN(LTRIM(Tbl1.Field2)) = 0 OR
                     Tbl1.Field2 IS NULL
                THEN 0
                ELSE 1 END) AS Tbl1_Fld2,
       MAX(CASE WHEN LEN(LTRIM(Tbl2.Field1)) = 0 OR
                     Tbl2.Field1 IS NULL
                THEN 0
                ELSE 1 END) AS Tbl2_Fld1
-- .....
FROM Tbl1
LEFT JOIN Tbl2 ON Tbl1.Id = Tbl2.Id
GROUP BY Tbl1.Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DTJeff]

Hi.

Thanks for that - I looked at the CASE function and have also come up with the following:

SELECT ID,
'Tbl1_Fld1'= CASE WHEN Tbl1.Field1 <> '' THEN '1' ELSE '0'
'Tbl1_Fld2'= CASE WHEN Tbl1.Field2 <> '' THEN '1' ELSE '0'
'Tbl2_Fld1'= CASE WHEN Tbl2.Field1 <> '' THEN '1' ELSE '0'
END,
FROM Tbl2 RIGHT OUTER JOIN
Tbl1 ON Tbl2.ID = Tbl1.ID LEFT OUTER JOIN
Tbl3 ON Tbl1.ID = Tbl3.ID LEFT OUTER JOIN
etc...

Which allows for checking for other strings by adding a When clause.

Thanks again for your help.

Jeff.