Joining Multiple Query Sets in SQL

[imavp2]

Hi. I am new to this forum/community and need help to join multiple result sets in SQL. The last time I used SQL was in 2000. The goal is to provide one single data set result from multiple queries. I have included the code from two queries that I have developed. I want to have a single query that provides last_name, first_name, add_date, PO_type, and new customers added by all personel in the same department. I have tried UNION operator which does not work for me and not sure about how to use CASE. Listed below are examples of the code.

QUERY 1:[/B]

SELECT
a.Last_name,
a.First_name,
b.Add_date,
COUNT(c.PO_Type) AS Blanket,
COUNT(c.PO_Type) AS [Spot_buy]
FROM Plexus_control_v_Plexus_user a
JOIN Common_v_Position d
ON a.Position_Key = d.Position_key
JOIN Sales_v_PO b
ON a.Plexus_user_no = b.add_by
JOIN Sales_v_PO_type c
ON b.PO_type_key = c.PO_type_key
AND c.PO_type = 'blanket'
OR c.PO_type = 'spot buy'
AND d.Position IN ('Customer Service')
WHERE b.add_date BETWEEN '07/01/2010' AND '07/16/2010'
GROUP BY a.Last_name, a.First_name, d.Position, b.add_date
ORDER BY a.Last_name, a.First_name

Query 2:

SELECT
a.First_name,
a.Last_name,
b.Add_date,
COUNT(c.Customer_Code) AS [New Customer Added]
FROM Plexus_control_v_Plexus_user a
JOIN Common_v_Position d
ON a.Position_Key = d.Position_key
JOIN Common_v_Customer b
ON a.Plexus_user_no = b.add_by
JOIN Common_v_Customer c
ON b.customer_no = c.customer_no
WHERE b.add_date BETWEEN '07/01/2010' AND '07/16/2010'
AND d.Position IN ('Customer Service')
GROUP BY a.First_name, a.Last_name, b.Add_date
ORDER BY a.First_name, a.Last_name

Any help would be greatly appreciated and please tell me what I am doing wrong.
Thanks so much.
ImaVP2

[marh]

I am not sure but try using the 'and' and 'or' selection after the where clause

[tesuji]

Hi

there arise two problems with your both single selects when unionizing them together:

1. only the last select is allowed to have an ORDER BY, therefore:

SELECT a, b, count (c) as "MasterHeadline" ...  group by ....
union
select a, b, count (d) as "not that important name" ... group by .....
ORDER BY  "MasterHeadline" ;  -- no further order bys allowed

2. All selects must be union-compatible, that is a) corresponding columns must have same or similar datatype which can be automatically casted into common DT, and important for your selects: b) the number of columns must be same.

You don't have same numbers of columns, so you must add a placeholder. Simplest placeholder is NULL:

SELECT a, b, count(c), sum(d) from ...
union
select a, b, count(d), NULL from ...

You can replace placeholder by any string, also empty string '', but then sum(d) must be type casted into string too:

SELECT a, b, count(c), CAST(sum(d) AS VARCHAR(20)) as "total" from ...
union
select a, b, count(d), 'Placeholder' as "total" from ...

This shows also a way to distinguish between both resultsets, as for example (using your [New Customer Added]):

SELECT a, b, count(c), CAST(sum(d) AS VARCHAR(20)) as "total", 'from 1st query' as "Where from" from ...
union
select a, b, count(d), 'Placeholder' as "total", 'New Customer Added!' as "Where from" from ...

You know the difference between double marks " " and single marks ' '.

-- tesu

[imavp2]

Hi Tesu.

Yes, I follow you clearly. I realized that in order to use UNION I had to have the corresponding columns and last nite I was thinking along the lines of adding a literal string. I will play some more with it tonite.

Thanks Tesu.

Imavp2