Show records where no relationship is available

• Mick Moses

  SSC Enthusiast

  Points: 158

  More actions

  January 20, 2011 at 6:52 am

  #230690

  Stupid novice question.

  I have 2 related tables a budget table and a customer hierarchy table.

  Both tables are setup via a manual CSV load.

  What has happened is there are budget figures for customers that do not exist and I need to find them.

  I've been trying to use left join but this returns all budget records regardless of customer existence or not.

  What I want is a list of budget records where the customer does not exist.

  I'm new to SQL, working in Progress 4GL so am quite frustrated that I could do this in seconds in Progress.

  Thanks

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  January 20, 2011 at 6:56 am

  #1275885

  This simplest methods of this are

  1) Use "not exists"

  Select Id from table1 where not exists(Select childid from table2 where Table2.ParentId = Table1.ID)

  or

  2) Left outer join

  Select Table1.Id from Table1 left outer join Table2

  on Table1.Id = Table2.ParentId

  Where Table2.ChildId is null

  ---

  
  Clear Sky SQL
  My Blog[/url]

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  January 20, 2011 at 6:56 am

  #1275886

  try a LEFT JOIN from your budget table, and add a where clause for customer is null

  eg..

  Select *

  from budget left join customer on budget.customerid = customer.customerid

  where customer.customerid is null

• Mick Moses

  SSC Enthusiast

  Points: 158

  More actions

  January 20, 2011 at 7:03 am

  #1275892

  WOW - That was quick.

  I'll try those out and let you know.

  Thanks guys.

• Mick Moses

  SSC Enthusiast

  Points: 158

  More actions

  January 20, 2011 at 7:56 am

  #1275950

  Excellent!!!!!

  I used the not exists version. As usual there's not just one field that links the two tables so here is my final version.

  This sums the figure to cross reference with the DW but the columns can also be shown if required.

  select SUM(BudgetAmount)

  from FactBudget

  where FactBudget.Entity = 'rhp'

  and FactBudget.BudgetName = 'forecast'

  and YEAR(FactBudget.BudgetDate) = '2011'

  and not exists (select *

  from DimGenericHierarchy

  where DimGenericHierarchy.HierarchyKey1 = FactBudget.Entity

  and DimGenericHierarchy.HierarchyName = 'Customer'

  and DimGenericHierarchy.HierarchyLevel5 = FactBudget.CustomerSubCategory)

  A little bit more knowledge & experience for me. Thanks again.