Return Rows, when two conditions that are true

Juan E. Ramirez 21 Reputation points
2021-11-24T16:03:38.123+00:00

The following query works fine in returning data as expected:

SELECT DISTINCT
convert(varchar, b.bookindt, 101) as [book-in date],
b.bookinno as [book-in no.],
dbo.fn_getoffensedesc(o.offenseid, o.probviolation,(select offense from trdcode61 where code61id = o.code61id) , o.goc) as offensedescription ,
o.PrimaryOffense AS [Primary Offense],
trd.l_d as [offense l/d],
p.firstname as [first name],
p.lastname as [last name]

from tblpeople p
left outer join tbloffense o (nolock) on o.personid=p.personid
left outer join tblbookin b (nolock) on b.bookinid=o.bookinid
left outer join trdcode61 trd (nolock) on trd.code61id=o.code61id

where
dbo.fn_isinjailbybookinid(b.bookinid) = 1
--AND
--b.bookinno='21042173'
AND
(trd.l_d like 'F%' or trd.l_d like 'M%')

order by
p.lastname, p.firstname

But, I need it to look at a b.bookinno AND if trd.l_d has an "F%" and "M%" return that b.bookinno. This, (trd.l_d like 'F%' AND trd.l_d like 'M%'), returns no hits.

152256-data.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,435 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,481 Reputation points
    2021-11-25T04:23:37.097+00:00

    If I understand what you want, then try

    ;With cteDRank As
    (SELECT DISTINCT convert(varchar, b.bookindt, 101) as [book-in date],
    b.bookinno as [book-in no.],
    dbo.fn_getoffensedesc(o.offenseid, o.probviolation,(select offense from trdcode61 where code61id = o.code61id) , o.goc) as offensedescription ,
    o.PrimaryOffense AS [Primary Offense],
    trd.l_d as [offense l/d],
    p.firstname as [first name],
    p.lastname as [last name],
    Dense_Rank() Over(Partition By bookinno Order By Left(trd.l_d, 1)) As DRank
    from tblpeople p
    left outer join tbloffense o (nolock) on o.personid=p.personid
    left outer join tblbookin b (nolock) on b.bookinid=o.bookinid
    left outer join trdcode61 trd (nolock) on trd.code61id=o.code61id
    
    
    where
    dbo.fn_isinjailbybookinid(b.bookinid) = 1
    --AND
    --b.bookinno='21042173'
    AND
    (trd.l_d like 'F%' or trd.l_d like 'M%')),
    
    cteMaxRank As
    (Select [book-in date],
      [book-in no.],
      offensedescription ,
      [Primary Offense],
      [offense l/d],
      [first name],
      [last name], 
      Max(DRank) Over(Partition By [book-in no.]) As MRank 
    From cteDRank)
    Select [book-in date],
      [book-in no.],
      offensedescription ,
      [Primary Offense],
      [offense l/d],
      [first name],
      [last name]
      From cteMaxRank
    Where MRank = 2  -- There was both at least one row with M% and at least one row with F% for this book-in no.
    order by
      [first name],
      [last name]
    

    Tom

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Viorel 119.6K Reputation points
    2021-11-24T18:46:42.143+00:00

    Try using this condition:

    where ( exists( select * from trdcode61 where code61id = o.code61id and l_d like 'F%')
        and exists( select * from trdcode61 where code61id = o.code61id and l_d like 'M%') )
    

  2. EchoLiu-MSFT 14,601 Reputation points
    2021-11-25T02:16:45.087+00:00

    Hi @Juan E. Ramirez

    Welcome to the microsoft TSQL Q&A forum!

    Please check:

    SELECT DISTINCT  
    convert(varchar, b.bookindt, 101) as [book-in date],  
    b.bookinno as [book-in no.],  
    dbo.fn_getoffensedesc(o.offenseid, o.probviolation,(select offense from trdcode61 where code61id = o.code61id) , o.goc) as offensedescription ,  
    o.PrimaryOffense AS [Primary Offense],  
    trd.l_d as [offense l/d],  
    p.firstname as [first name],  
    p.lastname as [last name]  
    from tblpeople p  
    left outer join tbloffense o (nolock) on o.personid=p.personid  
    left outer join tblbookin b (nolock) on b.bookinid=o.bookinid  
    left outer join trdcode61 trd (nolock) on trd.code61id=o.code61id  
    where  
    dbo.fn_isinjailbybookinid(b.bookinid) = 1  
    --AND  
    --b.bookinno='21042173'  
    AND trd.l_d in (select l_d from trdcode61 where l_d like 'F%'  
                    intersect   
                    select l_d from trdcode61 where l_d like 'M%')  
    order by  
    p.lastname, p.firstname  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.