0

Need some help using Tereadata I need to pull a report that joins many tables... Here is the basic concept:

Select Table1.ID, Table2.Arrival_date, Table2.Name
From Table1
     Inner Join Table1.ID on Table2.ID
Where table2.arrival_date between '10/01/2013' and '10/02/2013'

Then there is another table that includes the following information

ID     Item     Cost     Date
1      Flour    1.99     10/02/2013
2      cheese   3.99     10/01/2013
3      Flour    1.99     8/16/2013

I want to add this to the table above

Select Itemtable.ID, Itemtable.item, itemtable.cost, itemtable.date
from itemtable
where itemtable.date between '10/01/2013' and 10/02/2013
and itemtable.item like '%flour%'

Itemtable.ID = Table1.ID - but when I do a join I only get those customers who bought flour - I want all of the customers seen between that date frame and, if they bought flour want it - my final table would look like this:

ID    Arrival Date    Name     ID     Item     Cost     Date
1     10/01/2013       Dan
2     10/01/2013       Mike
3     10/01/2013       Nancy
1     10/02/2013       Dan      1      Flour   1.99     10/02/2013
5     10/02/2013       Mary

Any help would be appreciated!

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
user3046783
  • 1

1 Answers1

1

Typed roughly (I do not have the actual table definitions so I had to make this up on the fly), but basically you want to filter the sales first and then perform a left join from the customers; something like...

select Table2.ID, Table2.Arrival_dime, Table2.Name
from Table2
  left join (
    select Itemtable.ID, Itemtable.item, Itemtable.cost, ItemTable.date
    where where itemtable.date between '10/01/2013' and 10/02/2013
    and itemtable.item like '%flour%') itemTable on Table2.ID = itemTable.ID
where table2.arrival_date between '10/01/2013' and '10/02/2013'
Martin Robins
  • 6,033
  • 10
  • 58
  • 95