Re: report from two tables

[[EMAIL PROTECTED]]

Don't know how but it works now both ways. Don't ask me how!
:)

Thanks!

-afan



ISC Edwin Cruz wrote:

Try it:
   SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
   FROM v_orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND sm.Salesperson_No = o.SoldBy
UNION
   SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
   FROM orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND o.SoldBy = sm.User_ID
ORDER BY Trans_No DESC
LIMIT 100

It works for me.

Regards!

-----Mensaje original-----

De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 03 de Noviembre de 2005 03:55 p.m.

Para: MySQL List
Asunto: report from two tables


Hi,

I have two table for orders and I have to create a Report with list of orders from both tables for specific day. I made Reports for each table and they work just fine, but don't know how to "put them together":

Query for Order Table 1:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.User_ID FROM v_orders as
o, v_salesmen as sm WHERE o.Order_Date = '". $report_starts."' AND o.SoldBy
= sm.User_ID ORDER BY sm.LName ASC where Trans_No is transaction no., SoldBy

salesperson's no in table "orders", LName, FName and User_ID info from (third) table for salespersons (User_ID is salesperson's id - primary key).

Query for Order Table 2:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.Salesperson_No FROM
orders as o, v_salesmen as sm WHERE Order_Date = '". $report_starts."' AND
o.SoldBy = sm.Salesperson_No ORDER BY sm.LName ASC where Salesperson_No is
info from salespersons table - in this case FK.

I tried with this:

(
   SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
   FROM v_orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND sm.Salesperson_No = o.SoldBy
)
UNION
(
   SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
   FROM orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND o.SoldBy = sm.User_ID
)
ORDER BY Trans_No DESC
LIMIT 100

I'm NOT getting any error but either any result (entry)?

What am I doing wrong?

Thanks for any help.

-afan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]