Yes, I had tried it but it just show all the data combined together. I
need a data changed to another data in the same field.

Let say

Table A (detail file)

Item     uom       qty
ItemA    bag       10
ItemB    carton     20
ItmeC    piece      30
itemA     kg        40


Table B (conversion file)

Item     uom1    uom2     factor
itemA     bag     kg      50
itemB     kg     carton   30

(itemC do not have a record in  Table B)


so now I need a Table A Qty field data in 'KG' uom unless the item does
not have a record in Table B.  after conversion, Table A data should be
as below:

Table A (detail file)
Item   uom     qty
itemA  bag     500   calculation conversion= (10bag X 50kg), uom here is
not I in 'kg'
itemB  carton  0.667 cal. Conversion=>(20carton/30carton)
itemC  piece   30    qty remained unchanged
itemA  kg      40    qty reamin unchanged because the uom in 'kg', not
necessary to convert!.

I don't know how to write the SQL statement on above method.  
       
-----Original Message-----
rom: Joseph & Mary Taylor [mailto:joemary2@b...] 
Sent: Saturday, July 13, 2002 11:06 PM
To: sql language
Subject: [sql_language] RE: Help with Inner Join on Two Tables

Have you tried a LEFT JOIN. What a left join does is it includes all the
records from you employees table and those records that have taken leave
from the LeaveReq table.

Example, in the Northwind database, you have 2 tables - one called
Customers
and one called Orders. If we have a left join on the Customers table in
the
query below, you will get a count of all the orders made by all the
customers (even if they have not placed an order). You will get 91
records,
that is how many customers you will have in the Northwind database.
Enjoy.

SELECT Customers.CustomerID, Customers.ContactName,
Count(Orders.OrderDate)
AS CountOfOrderDate
FROM Customers LEFT JOIN Orders ON Customers.CustomerID 
Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.ContactName;



-----Original Message-----
From: anne.do@c... [mailto:anne.do@c...]
Sent: 10 July 2002 22:47
To: sql language
Subject: [sql_language] Help with Inner Join on Two Tables


This is my query:
SELECT EMPLOY.*, LeaveReq.Start, LeaveReq.End
FROM EMPLOY INNER JOIN LeaveReq ON EMPLOY.EMP_NO = LeaveReq.Emp_no WHERE
(((EMPLOY.EMP_NO) In (123,444,335))) ORDER BY EMPLOY.Last_Name;

I am trying to link two tables: Employ(Emp_No, First_Name, Last_Name)
and
LeaveReq (Record_No, Emp_No, Start, End).

I want to join the Employ table on Emp_No to the LeaveReq table.  The
query I have works fine if the Employee has requested Leave and has a
corresponding record in the LeaveReq table.  If Employee #335 in the
query
below has never requested leave and thus has no record in the LeaveReq
table, his record will not show up in the join.  How can I make SQL
still
include all Employees even if they have null values for all columns in
the
LeaveReq table?


Thanks,
Anne Do
anne.do@c...