Selecting records from multiple tables using Left Join

[JMay0816]

I have a tough query that I am trying to hammer through. Hoping someone can help. Trying to use three tables. So I start with this:

select a.field1, a.field2, b.field1 from table1 a
left join table2 b
on a.field1=b.field3
where a.field4='W' and a.field5='N'
group by a.field1, a.field2, b.field1

This currently returns 5 records:

a.field1 a.field2 b.field1
20701 Test Record 1
20704 Test Record 2 50789
20715 Test Record 3 50791
20733 Test Record 4
20751 Test Record 5 50811

Here's where it gets tricky. There is the third table (table3). Table2 and Table3 are linked by b.field1=c.field2. Table3 field8 is either a Y or an N).

So....still want all the records in Table1 however, I only want records in table2 if table3.field8='Y'. So my output should ultimately look like this:


20701 Test Record 1
20704 Test Record 2 50789
20715 Test Record 3
20733 Test Record 4
20751 Test Record 5

I can't seem to get the results when I try to add the third table. Can someone help me out?

Please let me know if you need more information.

Thx

 

[towerbase]

Can you show us the SELECT statement that is not returning your required results?

Andrew
Hampshire, UK