Geek asks and answers

SQL to select a parent that contains a child value

I am actually creating a report on Crystal Reports v12 (2008), but I cannot find a method using Crystal to extract the following. I thought that if someone could answer in SQL, I could put 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'

Output Example:

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 show Paul in the report with all the child records, but Peter should not be returned to the report, since he does not have child records with "2" for the ddmast.status field.

thanks for the help

+3
source share
3 answers

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

, dd, status = 2, hb dd ( ). SQL :

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 , 2. , sql .

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

: {%sqlexpression} > 0

+2

, :

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'
)

, .

+4

And another way to get the same ...

SELECT hb.custno, hb.id, dd.name, dd.status 
FROM hbmast hb
INNER join ddmast dd 
  on hb.custno = dd.custno
INNER JOIN DDMAST2 DD2 
  on DD2.custNo = HB.custNo 
  AND DD2.Status='2'
+1
source

More articles:


All Articles