Join Tables Exclude records based on child record field

[angjbsca]

Table 1
f1 f2 f3
1 hey hoy
2 how hew
3 hic hoc
Table 2
f1 t1fk-f2 f3
1 1 T
2 1 Y
3 3 Y
I need to meke a join but if one child record has 'T' in f3 then exclude all the records for the parent child relation.

my result in the example need to be:
Hic Hoc Y

 

[jbenson001]

Try:

Select t1.f2, t1.f3, t2.f3
From table1 t1
   InnerJoin table2 t2 ON t1.f1 = t2.[fk-f2] AND
      t2.f3 <> 'T'

Jim

P.S. This is not tested.

 

[gmmastros]

Try something like this...

Declare @Table1 Table(f1 Integer, F2 VarChar(10), F3 VarChar(10))
Insert Into @Table1 Values(1,'hey','hoy')
Insert Into @Table1 Values(2,'how','hew')
Insert Into @Table1 Values(3,'hic','hoc')

Declare @Table2 Table(F1 Integer, [T1fk-f2] Integer, F3 VarChar(10))
Insert Into @Table2 Values(1,    1,      'T')
Insert Into @Table2 Values(2,    1,      'Y')  
Insert Into @Table2 Values(3,    3,      'Y')

Select T1.*
From   @Table1 As T1
       Inner Join (
         Select [t1fk-f2]
         From   @Table2
         Group By [t1fk-f2]
         Having Count(1) = Sum(Case When F3 = 'T' Then 0 Else 1 End)
       ) As A On T1.F1 = A.[t1fk-f2]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[angjbsca]

good job I'v been working all day in that....

you take out all records with "T" then join, It's all corret?

 

[gmmastros]

I think that Jim's query does not produce the correct results.

With my method, we count the number of records and effectively count the number of records where there is anything other than a 't'. If the numbers match, then I use the data as a subquery to join back to the original table and pull the data you want.

I hope this makes sense.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Yes, test my code, as I have indicated I didn't test it...

 

[angjbsca]

thank you for you help.