afanafan.net
Date: Thu Nov 03 2005 - 16:26:30 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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: afanafan.net [mailto:afanafan.net]
>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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]