CODEWITHSUNDEEP
sql
macunte
macunte

Reputation: 465

Return all child records related to parent

I have an ACCOUNT table with the following fields: Acct#, Name

and child table CFALTN with fields: cfseq, cfaal1, cfacc#

I want to query and link primary to all child records in the following example:

TABLE1
Acct#, Name
2244,  Joe 
1890,  Jack
0223,  Sam
9898,  Mary

TABLE2
cfseq, cfaal1, cfacc#
1, Joe and bens farm, 2244
2, POD mary and ben,  2244
1, POD Jay Williams,  1890
1, POD Ronnie future farm, 0223
2, for raising goats, 0223
1, OR James Russell, 9898

I'm wanting to query where POD is found in the child but not just return the child record that has POD, but both child records.

SELECT T1.acct#, T1.name, T2.cfseq, T2.cfaal1, T2.cfacc#
FROM data.table1 T1
    JOIN  data.table2 T2 WHERE T1.acct# = T2.cfacc# 
AND T2.cfaal1 like '%POD%'

DESIRED RESULTS:
2244, Joe, 1, Joe and bens farm, 2244
2244, Joe, 2, POD mary and ben,  2244
1890, Jack, 1, POD Jay Williams, 1890
0223, Sam, 1, POD Ronnie future trust, 0223
0223, Sam, 2, for education wing, 0223

Upvotes: 1

Views: 60

Answers (1)

ben
ben

Reputation: 168

SELECT T1.acct#, T1.name, T2.cfseq, T2.cfaal1, T2.cfacc#
FROM data.table1 T1
JOIN  data.table2 T2 ON T1.acct# = T2.cfacc# 
where
    T1.acct# in (select cfacc# from T2 where cfaal1 like '%POD%')

Upvotes: 1

Related Questions