Results 1 to 2 of 2

Thread: Multiple Joins

  1. #1
    Join Date
    Jun 2005
    Location
    Lancashire UK
    Posts
    5

    Question Unanswered: Multiple Joins

    Hello,

    I need to create a view which links 5 tables as follows:
    I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

    I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

    Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
    Best regards
    Colin

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    If there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

    If I'm understanding your intention, the following (untested) should work :

    Code:
    select 	h.*,
    	d.*,
    	s.*,
    	de.*
    from 	Header h left outer join
    	Detail on h.product = d.product and h.year = d.year inner join
    	sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
    	Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
    	Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week
    Perhaps I'm misunderstanding what you're wanting to see...
    Last edited by onansalad; 02-03-06 at 10:48.
    Kit Lemmonds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •