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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
