SQL joining 2 queries that share a column

Question

I have 2 SQL queries that share a column called catalogid

Query #1:

Select 
   catalogid, numitems, allitems - numitems ignoreditems
from
    (select 
        i.catalogid,
 sum(case when (ocardtype in ('PayPal','Sofort') OR
                ocardtype in ('mastercard','visa') and
                odate is not null) then numitems
                else 0 end) numitems,
 sum(numitems) allitems
from orders o"
join oitems i on i.orderid=o.orderid"
join products T1 on T1.catalogid = i.catalogid"
group by i.catalogid) X

Query #2:

SELECT catalogId, ProcessedSucssessfully =
       STUFF((SELECT ', ' + CAST( b.orderid as varchar(10))
              FROM oitems b JOIN orders o ON b.orderid = o.orderid
              WHERE b.catalogId = a.catalogId
              AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in  ('mastercard','visa') and o.odate is not null)
              FOR XML PATH('')), 1, 2, ''),
                  NotProcessed =
        STUFF((SELECT ', ' + CAST( c.orderid as varchar(10))
               FROM oitems c JOIN orders o ON c.orderId = o.orderid
               WHERE c.catalogid = a.catalogid
               AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null
               FOR XML PATH('')), 1, 2, '')
FROM oitems a
GROUP BY a.CatalogId

How would you make those 2 into one query or join them?

Note I run those 2 as SqlCommand from vb.net

One thing to notice is i've the same conditions for both queries, what I tried doing is adding the second query parts to the select case which didn't work out

Here are the involved tables

oitems table

+---------+-----------+----------+
| orderid | catalogid | numitems |
+---------+-----------+----------+
| o737    |       353 |        1 |
| o738    |       364 |        4 |
| o739    |       353 |        3 |
| o740    |       364 |        6 |
| o741    |       882 |        2 |
| o742    |       224 |        5 |
| o743    |       224 |        2 |
+---------+-----------+----------+

orders table

+-----------------+------------+------------+
|         orderid | ocardtype  |   odate    |
+-----------------+------------+------------+
|     o737        | Paypal     |            | 'OK
|     o738        | MasterCard | 01.02.2012 | 'OK
|     o739        | MasterCard | 02.02.2012 | 'OK
|     o740        | Visa       | 03.02.2012 | 'OK
|     o741        | Sofort     |            | 'OK
|     o742        |            |            | 'ignore because ocardtype is empty
|     o743        | MasterCard |            | 'ignore because Mastercard no odate
+-----------------+------------+------------+

result from Query #1:

+-----------+----------+--------------+
| catalogid | numitems | ignoreditems |
+-----------+----------+--------------+
|       353 |        4 |            0 |
|       364 |       10 |            0 |
|       882 |        2 |            0 |
|       224 |        0 |            7 |
+-----------+----------+--------------+

result from Query #2:

+-----------+------------------------+--------------+
| catalogid | ProcessedSucssessfully | NotProcessed |
+-----------+------------------------+--------------+
|       353 |o737,o739               |              |
|       364 |o738,o740               |              |
|       882 |o741                    |              |
|       224 |                        |o742,o743     |
+-----------+------------------------+--------------+

the wanted result:

+-----------+-----------+--------------+-------------------------+---------------+
| catalogid | numitems  | ignoreditems | ProcessedSucssessfully  | NotProcessed  |
+-----------+-----------+--------------+-------------------------+---------------+
|       353 |         4 |            0 | o737,o739               |               |
|       364 |        10 |            0 | o738,o740               |               |
|       882 |         2 |            0 | o741                    |               |
|       224 |         0 |            7 |                         | o742,o743     |
+-----------+-----------+--------------+-------------------------+---------------+

The conditions for Query1:

if ocardtype is empty then ignore the numitems and consider it as 0 in the sum and sum the ignored items to the ignoreditems column
if ocardtype for some order is MasterCard or Visa and the odate is empty then ignore the numitems and consider it as 0 and sum the ignored items to the ignoreditems column
if ocardtype is Paypal or Sofort, then just do the numitems sum without checking the date because those types require no odate

The conditions for Query2 Which are the same as Query1 but different things to be done:

if ocardtype is empty then add orderid to NotProcessed
if ocardtype for some order is MasterCard or Visa and the odate is empty then add orderid to NotProcessed
if ocardtype is Paypal or Sofort, then don't check for odate and add the orderid to ProcessedSucssessfully

The above things are done in 2 seperate queries, but I am trying to make it in to one query since they have the same conditions

@RedFilter i tried adding the parts of query 2 to the select case of query1 since they have the same conditions but with no luck — user1570048, Oct 03 '12 at 20:04

score 2 · Answer 1 · edited Oct 03 '12 at 14:57

2

You could create a view on the sql box and then just Linq to that view

select * from table_a inner join table_b on table_a.field_a = table_b.field_b

EDIT:

or

Select * from (select * from table_a inner join table_b on table_a.field_a = table_b.field_b) AB
inner join
select * from (select * from table_c inner join table_d on table_c.field_c = table_d.field_d) CD
ON
AB.id_column = CD.id_column

edited Oct 03 '12 at 14:57

marc_s

732,580
175
1,330
1,459

answered Oct 03 '12 at 14:44

Thor Burfine

124
1
7

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Oct 03 '12 at 14:57
@thor both queries share the same conditions but i am not sure how i can add the second Query#2 part to the select statment in Query#1, tried doing it but got syntax errors – user1570048 Oct 03 '12 at 15:18
can you script the tables involved and put the SQL up – Thor Burfine Oct 03 '12 at 16:38

score 1 · Answer 2 · answered Oct 03 '12 at 20:32

SQL server view would look something like this

SELECT * FROM
(
    Select  
       catalogid, numitems, allitems - numitems ignoreditems 
    from 
        (
        select * from
        (
            select  
                i.catalogid, 
                sum(case when (ocardtype in ('PayPal','Sofort') 
                OR 
                ocardtype in ('mastercard','visa') and odate is not null) then numitems 
                else 0 end) numitems, 
                sum(numitems) allitems 
            from 
                orders
         ) o
        inner join
            items i 
        on 
            i.orderid=o.orderid
        inner join 
            products T1 
        on 
            T1.catalogid = i.catalogid
        group by 
            i.catalogid
        ) A
    ) X 
) B
INNER JOIN
SELECT * FROM
(
    SELECT 
        catalogId, 
        ProcessedSucssessfully = 
           STUFF((SELECT ', ' + CAST( b.orderid as varchar(10)) 
                  FROM oitems b JOIN orders o ON b.orderid = o.orderid 
                  WHERE b.catalogId = a.catalogId 
                  AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in  ('mastercard','visa') and o.odate is not null) 
                  FOR XML PATH('')), 1, 2, ''), 
                      NotProcessed = 
            STUFF((SELECT ', ' + CAST( c.orderid as varchar(10)) 
                   FROM oitems c JOIN orders o ON c.orderId = o.orderid 
                   WHERE c.catalogid = a.catalogid 
                   AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null 
                   FOR XML PATH('')), 1, 2, '') 
    FROM 
        oitems a 
    GROUP BY 
        a.CatalogId 
) B
ON A.CatalogId = B.CatalogId

thank you so much, but it gives me syntax error near ) and near on, i think its because we have 24 '(' and 25 ')' )(31,1) expected token:Unknown )(31,1) expected token: ON(54,1) expected token:; JOIN ( ) — user1570048, Oct 03 '12 at 20:51
Could be, I did not validate against the the table structure. Sorry for the slow response. I had to cut and run early yesterday. — Thor Burfine, Oct 04 '12 at 12:09
not a problem thank you for taking time to write it,i've been trying to find what is wrong with the syntax but couldn't figure it out — user1570048, Oct 04 '12 at 12:33

score 1 · Accepted Answer · answered Oct 04 '12 at 12:37

Ok here we go, I built out the table structure to validate the SQL. I did not load any data but it does execute.

SELECT * FROM
(
Select  
   catalogid, numitems, allitems - numitems ignoreditems 
from 
    (
        select  
            i.catalogid, 
            case 
                when ocardtype in ('PayPal','Sofort') then sum(i.numitems)
                when ocardtype in ('mastercard','visa') and odate is not null then sum(i.numitems)
            else 0 end numitems, 
            sum(numitems) allitems 
        from 
            orders o
        inner join
            oitems i 
        on 
            i.orderid=o.orderid
        inner join 
            products T1 
        on 
            T1.catalogid = i.catalogid
        group by 
            i.catalogid, ocardtype, odate
    ) A
) B
INNER JOIN
(
    SELECT 
        catalogId, 
        ProcessedSucssessfully = 
           STUFF((SELECT ', ' + CAST( b.orderid as varchar(10)) 
                  FROM oitems b JOIN orders o ON b.orderid = o.orderid 
                  WHERE b.catalogId = a.catalogId 
                  AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in  ('mastercard','visa') and o.odate is not null) 
                  FOR XML PATH('')), 1, 2, ''), 
                      NotProcessed = 
            STUFF((SELECT ', ' + CAST( c.orderid as varchar(10)) 
                   FROM oitems c JOIN orders o ON c.orderId = o.orderid 
                   WHERE c.catalogid = a.catalogid 
                   AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null 
                   FOR XML PATH('')), 1, 2, '') 
    FROM 
        oitems a 
    GROUP BY 
        a.CatalogId 
)C
    ON 
        B.CatalogId = C.CatalogId

Now if you want to make it a stored procedure... add to the top of the sql create procedure usp_procedure_name ( @OID VARCHAR(50) ) AS Then add a where clause to each part of the sql — Thor Burfine, Oct 04 '12 at 12:38

SQL joining 2 queries that share a column

3 Answers3