Return Rows, when two conditions that are true-CodePudding

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

Here is a screenshot of results for a single "bookinno" to be clearer:

I need help with a query, where it looks at the "Book-in No." column AND if the "Offense l/d" column includes an (trd.l_d like 'F%' AND trd.l_d like 'M%') then return that "Book-in No." result.

When I try to run with AND I get zero results.

I do have "Book-in No." with only "F%" or only "M%".

Would appreciate any help.

Regards, jer

CodePudding user response：

You can filter the resultset with your conditions, group by bookinno and set the condition in the HAVING clause so that both conditions apply to that bookinno:

SELECT b.bookinno AS [book-in no.]
FROM tblpeople p
INNER JOIN tbloffense o ON o.personid = p.personid 
INNER JOIN tblbookin b ON b.bookinid = o.bookinid 
INNER JOIN trdcode61 trd ON trd.code61id = o.code61id 
WHERE dbo.fn_isinjailbybookinid(b.bookinid) = 1 
  AND (trd.l_d LIKE 'F%' OR trd.l_d LIKE 'M%')
GROUP BY b.bookinno
HAVING COUNT(DISTINCT LEFT(trd.l_d, 1)) = 2;

Note that the joins for this requirement should be INNER and not LEFT because you don't want in the results any unmatched rows.