How to use outerjoin in access query?

[victorlui]

What is the syntax in SQL view of query of the below outer join part (Oracle -> Access) ?

SELECT "T_ITEM"."ITEM_CD",
"T_ITEM"."ITEM_DESC",
"T_ITEM"."LAST_PO_DATE",
"T_ITEM"."LAST_MOVE_DATE",
"T_ITEM"."PART_TYPE",
sysdate a ,
'' as b,
'' as c,
'' as company_name1,
'' as company_name2,
'' as company_dept_line,
'' as userid,
A.creation_date,
t_inventory_tran.TRANX_QTY
FROM "T_ITEM",
(select max(creation_date) creation_date,
item_cd
from t_inventory_tran
where tranx_ref_no like 'WRT%'
group by item_cd
) A,
t_inventory_tran,
T_BIN_LOCATION
WHERE rtrim("T_ITEM"."ITEM_STATUS") = 'W' AND
"T_ITEM"."ITEM_CD" = A.ITEM_CD (+) and
t_inventory_tran.tranx_ref_no like 'WRT%' and
A.item_cd = t_inventory_tran.item_cd and
to_char(A.creation_date,'yyyy/mm/dd hh:mm:ss') =
to_char(t_inventory_tran.creation_date,'yyyy/mm/dd hh:mm:ss')
;

 

[Pat Hartman]

Outer joins come in two types LEFT and RIGHT. You would need to list all the tables, their primary keys and how they are related to get a definitive answer as to what the join that you need looks like.

However, the query as you have it shown will NOT work if run by Oracle nor will it run as an Access query. You have made a mongrel of the two different syntaxes (sic). Start again. Link to the Oracle table if you don't already have a link to it. Then open the query builder. Add all the tables that you want to include. Draw the appropriate join lines connecting the primary and foreign keys in the related tables. Every table must be connected to at least one other table or you'll end up with a cartesian product. After you draw a join line, you can right click on it and change its properties to LEFT or RIGHT. The default is INNER. Once all the tables are linked, just select the columns that you want from each table.

Either remove the table - T_BIN_LOCATION - or select some column from it. Right now it serves no purpose.

 

[victorlui]

How about the conversion of subquery in from clause?

 

[Pat Hartman]

The following query will return multiple rows if there are multiple tranx_ref_no values for the same date.


Select creation_date, item_cd, tran_ref_no
From t_inventory_tran Where creation_date =
(Select Max(creation_date) As MaxDate
From t_inventory_tran
Where tranx_ref_no LIKE "WRT*");

The subquery finds the max date for the selection criteria and the outer query returns the rest of the data associated with the max date record.