Message-ID: <46A955FC.8000105@autoledgers.com.au>
Date: Fri, 27 Jul 2007 10:18:36 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 2.0.0.5 (Windows/20070716)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Join question
References: <00a001c7cff0$300ff000$9b0014ac@wbaus090>
In-Reply-To: <00a001c7cff0$300ff000$9b0014ac@wbaus090>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Phillip Smith wrote:
> Whoops, I forgot the JOIN conditions! Fixed below
> 
> -----Original Message-----
> From: Phillip Smith [mailto:phillip.smith@weatherbeeta.com.au] 
> Sent: Friday, 27 July 2007 11:47
> To: 'pgsql-sql@postgresql.org'
> Subject: RE: [SQL] Join question
> 
> 
> This might give you a starting point if I understand you correctly...
> 
> SELECT h.invoice_number,
> 	 h.customer,
> 	 l.item,
>  	 l.amount
> FROM	lines AS l
> JOIN	headers AS h ON l.invoice_number = h.invoice_number
> UNION
> SELECT h.invoice_number,
> 	 h.customer,
> 	 s.item,
>  	 s.amount
> FROM	sundries AS s
> JOIN	headers AS h ON s.invoice_number = h.invoice_number
> ORDER BY invoice_number, item
> 

Forgot all about union - I had two individual views, one for 
invoice+parts, one for invoice+sundries... didn't think of union to 
combine the two views together.

Perfect solution, you've done it for me again Phillip, thanks.

-- 
Paul Lambert
Database Administrator
AutoLedgers