Hi,

One thing here is very clear.
You need to make updated to different tupples, depending
on some conditions.
Well, updates can not be done, for all of the tupples, at the same time
(i.e. making one only update query for taking care of all of these
condition directly).
This is a very good case where one should use a Stored Procedure for
solving this kind of problem (or a trigger depending on your design;
the trigger in that case would fire of when a change would take place
on "Table B").

For solving the actual problem, i.e. the query for making these changes
you need to (or maybe better to say can use! there are always many diff.
ways to solve a problem) use "CASE - statements" for determining wich
of the tupples the current record/tupple is refering to (e.g. ItemA or ItemB
etc.).

I hope that you unerstand what i am trying to say and I also hope
that this is what you where looking for!

If you still don?t know how to make the query, tell me and i will show you.

Regards
/ Ali Karimi




-----Ursprungligt meddelande-----
Fran: Jane Choo [mailto:sschoo@a...]
Skickat: den 15 juli 2002 02:28
Till: sql language
Amne: [sql_language] RE: Help with Inner Join on Two Tables


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