Return a 1 if any row in jpined table has data

Hi

To simplify, I have 2 tables joined on ID. The second table has several columns containing different type of comment. and can have several rows joined to the first table

What I need is if any of the comment fields have a value (Len(comment1)>0 or Len(comment2) >0 ) I return a flag of 1 in the dataset.

What I'm finding is that I get a row for each in the joined comment table, I just want a flag indicating that any row in the joined table has a comment in any field.

Andy
LVL 3
Andy GreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try like:

Select a.ID,
case when len(b.comment1)>0 or len(b.comment2) >0  then 1 else 0 end flag
from yourTable a inner join anotherTable b on a.ID = b.ID

Open in new window

?
0
Mayank GairolaSr. Application Support Engg.Commented:
select 1
from table1
where exists (select top 1 1 from table2 where table1.id=table2.id and ((Len(comment1)>0) or (Len(comment2)>0)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use a CASE statement:
SELECT t1.ID, CASE
                WHEN  (Len(t2.comment1)>0 OR Len(t2.comment2) >0 ) THEN 1
                ELSE 0
         END CASE AS HasComments
FROM table1 t1
    INNER JOIN table2 t2 ON t1.ID = t2.ID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andy GreenAuthor Commented:
Thanks all, Ryans was the only way that worked, in that it returned just 1 row with the flag set.

This is my working SQL, question is how effiecient is this and is a sub select or a function a better way to include this into my existing SQL.

Select 1

From bpOutpat_view where exists (select top 1 1 from EventComments where  bpOutpat_view.id = EventComments .ID and (Len(EventComments .Notes)>0 OR Len(EventComments .RequestComments) >0 ))




Andy
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I had an extra CASE keyword in my solution. Should be:
SELECT t1.ID, CASE
                WHEN  (Len(t2.comment1)>0 OR Len(t2.comment2) >0 ) THEN 1
                ELSE 0
         END AS HasComments
FROM table1 t1
    INNER JOIN table2 t2 ON t1.ID = t2.ID

Open in new window

Anyway is mostly the same as Ryan's. Didn't see his answer when I was posting mine.
0
Andy GreenAuthor Commented:
Thanks Vitor I did spot that, but yours returns a row for each comment, I need Ryans where I only get 1 row back with the flag set.

Andy
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange, they are exactly the same.
Or do you mean Mayank's solution?
0
Andy GreenAuthor Commented:
Sorry - yes I mean Mayanks :-)

Andy
0
Scott PletcherSenior DBACommented:
EXISTS will be the most efficient way:

SELECT t1.<col>, ...,
    CASE WHEN EXISTS(
        SELECT 1
        FROM table2 t2
        WHERE
            t2.key_col = t1.key_col AND
            (t2.comment1 > '' OR t2.comment2 > '')
        ) THEN 1 ELSE 0 END AS flag
FROM table1 t1
WHERE t1.<col> = value ...
0
John EsraeloDatabase / SQL DeveloperCommented:
Add the len of all the comment fields together as a new column, write the result to a temp table or use CTE then filter the records that have a non zero len

e.g

;with TheSizes as
(
 select f1, f2, comment1, comment2, len(comment1)+len(comment2) TotalCommentLen from mytable and joined tables)
select * from TheSizes where TotalCommentLen > 0

this way you don't need flags

just a thought


JohnE
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
John, the requirement is " if any of the comment fields have a value (Len(comment1)>0 or Len(comment2) >0 ) I return a flag of 1 in the dataset." so your solution won't work if one of the comments is null.
0
Andy GreenAuthor Commented:
I also need to return the flag tot he asp.net page for processing.
0
Andy GreenAuthor Commented:
Thanks All.

Always difficult with points for multiple responses, but I am using Mayanks code, and Scott confirmed the Exists was the way to go.
0
John EsraeloDatabase / SQL DeveloperCommented:
Victor,

That's true, but, the methods such as isnull( , ) and coalesce () are there and available to use for that reason.


just a thought, and it seems like the question has been answered anyway.

JohnE
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.