Add condition to query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add condition to query

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2010-03-18 : 14:36:34

Hi all, I have the below query.

SELECT DISTINCT A.LNAME + ', ' + A.FNAME + ' ' + A.MNAME + ' ' + A.TITLE AS Name, CONVERT(varchar(10), A.BOOKDATE, 101) AS BOOKDATE, A.DAYSSERVED, B.STATUS, CASENO, COURTCASENUM
FROM  JBCASE AS B INNER JOIN
      JAILBOOK AS A ON B.BOOKNO = A.BOOKNO
WHERE (B.BOOKNO NOT IN
         (SELECT BOOKNO
            FROM JBCASE AS JBCASE_1
           WHERE (STATUS = 'SF') OR
                 (STATUS = 'SFF') OR
                 (STATUS = 'SM') OR
                 (STATUS = 'SMM') OR
                 (STATUS = 'BAK') OR
                 (STATUS = 'FAS') OR
                 (STATUS = 'FVOP') OR
                 (STATUS = 'JUV') OR
                 (STATUS = 'MAR') OR
                 (STATUS = 'MAS') OR
                 (STATUS = 'MVOP') OR
                 (STATUS = 'OTHE') OR
                 (STATUS = 'PAR') OR
                 (STATUS = 'STAT') OR
                 (STATUS = 'UNDO'))) AND (B.STATUS = 'MA') OR (B.STATUS = 'FAT')

All I need to do is add another condition to it. I need to display the column 'FSN' from table 'JBCHARGE' where fsn like '948.06%' but I can't figure it out. I tried this but it doesn't work.

SELECT DISTINCT A.LNAME + ', ' + A.FNAME + ' ' + A.MNAME + ' ' + A.TITLE AS Name, CONVERT(varchar(10), A.BOOKDATE, 101) AS BOOKDATE, A.DAYSSERVED,B.STATUS, B.CASENO, B.COURTCASENUM
FROM JBCASE AS B INNER JOIN
     JAILBOOK AS A ON B.BOOKNO = A.BOOKNO INNER JOIN
     JBCHARGE ON JBCHARGE.BOOKNO = B.BOOKNO
WHERE jbcharge.fsn like '948.06%' and  (B.BOOKNO NOT IN
        (SELECT BOOKNO
           FROM JBCASE AS JBCASE_1
          WHERE (STATUS = 'SF') OR
                (STATUS = 'SFF') OR
                (STATUS = 'SM') OR
                (STATUS = 'SMM') OR
                (STATUS = 'BAK') OR
                (STATUS = 'FAS') OR
                (STATUS = 'FVOP') OR
                (STATUS = 'JUV') OR
                (STATUS = 'MAR') OR
                (STATUS = 'MAS') OR
                (STATUS = 'MVOP') OR
                (STATUS = 'OTHE') OR
                (STATUS = 'PAR') OR
                (STATUS = 'STAT') OR
                (STATUS = 'UNDO'))) AND (B.STATUS = 'MA') OR
               (B.STATUS = 'FAT')

Thanks for your help.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-19 : 03:04:34

are you getting any error or not getting expected output
one more thing jbcharge.fsn like '948.06%' condition you can put in the join condition instead of where to keep your previous query as it is.

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources