SQL to select parent that contains child specific value

Question

I am actually creating a crystal reports v12 (2008) report but can't find the method, using Crystal, to extract the following. I thought if someone might answer in SQL language, I could piece it together.

2 Tables: hbmast, ddmast

SELECT hbmast.custno, hbmast.id, ddmast.name, ddmast.status
WHERE hbmast.custno = ddmast.custno
GROUP BY hbmast.id
pseudo code::show all hbmast values that have ddmast.status = '2'

Sample output:

J0001, 111222, PAUL JONES, 1
       111222, PAUL JONES, 2
       111222, PAUL JONES, 1

K0001, 555333, PETER KING, 3
       555333, PETER KING, 1

I would like to have Paul show on the report with all child records but Peter should not be returned on the report since he has no child records with '2' for ddmast.status field.

Thanks for the help

Answer 1

I think you're looking for this:

select hb.custno, hb.id, dd.name, dd.status from hbmast hb
join ddmast dd on hb.custno = dd.custno
where hb.custno in (
    select custno from ddmast
    where status = '2'
)

Let me know if this returns your expected result.

Answer 2

The way to achieve this in Crystal would be to have your hb and dd tables then a second alias of the dd table.

So you would filter your dd alias table where status = 2 then join to your hb table and back to your dd table (not the alias). The SQL would end up looking like:

select hb.custno, hb.id, dd.name, dd.status from hbmast hb
inner join ddmast dd on hb.custno = dd.custno
inner join ddmast dd2 on hb.custno = dd2.custno
where dd2.status = '2'

Andomar makes a valid point about duplicate records appearing if there is more than 1 record per group with a status of 2. If that is the case you can either group by primary key and show row information at group footer level OR use a sql expression with a subquery in your selection formula instead of the double join method.

SQL Expression: (select count(*) from ddmast where custno = "hbmast.custno" and status = '2')

Then record selection expert: {%sqlexpression} > 0