daniell

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

hugo kornelis

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)