Conditional Join

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
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)