left outer join 3 queries -- maybe?

[nevahj]

Hello all,


I have three queries that I need to join together and cannot figure out how. I cannot use a UNION since each query returns a different number of columns.

The first query returns 161 records and I want all these records with nulls in the fields added with queries 2 and 3 where there is no data.

Any help will be greatly appreciated!!

----------------------------------------------------------------------
QUERY 1 -- Returns 161 rows
----------------------------------------------------------------------
#customer data -- SLM = 110
select
  s.address_name as SALESMAN,
  c.slm_number, c.cust_code, c.ship_to_name,
  format(c.mtd_status_inv_sale,2) as MTD_SATUS,
  format((c.mtd_status_inv_sale - c.mtd_status_inv_cost) / c.mtd_status_inv_sale,2) as MTD_MARGIN,
  format(c.ytd_status_inv_sale,2) as YTD_STATUS,
  format((c.ytd_status_inv_sale - c.ytd_status_inv_cost) / c.ytd_status_inv_sale,2) as YTD_MARGIN,
  format(c.ly_sales,2) as LY_SALES,
  format((c.ly_sales - c.ly_cost) / c.ly_sales,2) as LY_MARGIN
from customer c, salesman s
where s.slm_number = c.slm_number and s.slm_number = 110
order by s.address_name, c.ytd_status_inv_sale desc;


----------------------------------------------------------------------
QUERY 2 -- Returns 89 rows
----------------------------------------------------------------------
#wbw_line data -- YTD -- SLM = 110
select
  w.slm_number, w.cust_code,
  DATE_FORMAT(w.INV_DATE, '%Y') AS "YEAR",
  format(sum((w.UT_PRICE * w.MULTIPLIER) * w.INV_QTY),2) as "GROSS_SALES"
from wbw_line w, salesman s
where s.slm_number = w.slm_number AND YEAR(w.inv_date) = YEAR(CURRENT_DATE) and s.slm_number = 110
group by s.slm_number, cust_code
order by s.address_name, GROSS_SALES desc;


----------------------------------------------------------------------
QUERY 3 -- Returns 96 rows
----------------------------------------------------------------------
#wbw_line data -- last year -- SLM = 110
select
  w.slm_number, w.cust_code,
  DATE_FORMAT(w.INV_DATE, '%Y') AS "YEAR",
  format(sum((w.UT_PRICE * w.MULTIPLIER) * w.INV_QTY),2) as "GROSS_SALES"
from wbw_line w, salesman s
where s.slm_number = w.slm_number AND YEAR(w.inv_date) = YEAR(CURRENT_DATE)-1 and s.slm_number = 110
group by w.slm_number, cust_code
order by s.address_name, GROSS_SALES desc;

Thanks,
Charles

 

[KarveR]

only a minor point but :
and s.slm_number = 110
group by s.slm_number

seems to be a little pointless and will probably affect what is returned, as you are asking for all s.slm_number to be 110 , if everything has the same number surely theres no point in grouping by it .. ?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[nevahj]

Yes, I know it is pointless to group ny salesman when I explictly ask for 110. In the end I will want to group and get all salesman. For now to keep things simple I am just asking for slsman 110.

- Charles