Re: Conditional Join

On Fri, 11 Nov 2005 12:17:02 -0800, Daniell wrote:

>What I have are two different tables that I am going to join. Everything in
>the first table should be pulled. The second table should only pull the
>information when the field ACTION equals the word "ORDERED". Is there a way
>to join two tables on just a literal
>
>Table A
>
>Tran Date_ORD Date_Rec ID Rate
>4756 10/23/05 99/99/99 J234 1.26
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 99/99/99 J234 4.26
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85
>
>Table B
>
>Tran Action
>4756 ORDERED
>7364 RECEIVE
>9834 CANCELE
>8374 BACKORD
>6756 ORDERED
>7263 RECEIVE
>1844 RECEIVE
>
>Output
>4756 10/23/05 10/25/05 J234 1.26 ORDERED
>7364 10/23/05 10/26/05 H342 3.23
>9834 09/23/04 10 05/04 J234 1.74
>8374 08/29/05 09/03/05 K834 2.85
>6756 09/21/05 09/24/05 J234 4.26 ORDERED
>7263 11/01/05 11/06/05 H342 2.23
>1844 10/02/05 10/05/05 J234 3.74
>2333 06/27/05 07/01/05 K834 5.85

Hi Daniell,

I think that this is what you want:

SELECT a.Tran, a.Date_ORD, a.Date_Rec, a.ID, a.Rate,
COALESCE(b.Action, '') AS Action
FROM TableA AS a
LEFT OUTER JOIN TableB AS b
ON b.Tran = a.Tran
AND b.Action = 'ORDERED'


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.