Combining two SQL queries selecting data from one table in to one query.

hi,

maybe using "or"to combine the 2:


SELECT "ARInvoices"."arpARInvoiceID",
"ARInvoiceLines"."arlSalesOrderID",
"ARInvoices"."arpInvoiceDate",
"ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines"."arlCustomerPO",
"ARInvoices"."arpCustomerOrganizationID",
"ARInvoices"."arpShipLocationID",
"OrganizationLocations"."cmlLocationID",
"OrganizationLocations"."cmlState",
"ARInvoices"."arpInvoiceTotalForeign",
"ARInvoices"."arpInvoiceTaxAmountForeign",
"OrganizationLocations"."cmlCountry"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30')) or ("ARInvoices"."UARPDEPOSITORFINAL" = 1.00
AND "ARInvoices"."arpInvoiceDate" < ('2007-07-26'))

Is there a way I can group or select by say "ARInvoiceLines"."arlCustomerPO"?

With the "OR" I get all the data in the first and I get all the data from the end date from the second.
So I am about 3/4 the way there. (THANKS!)
If I could match Customer PO in the two queries that would do it.
Since the two records I am looking for have the same customer PO #.

im sorry, i dont completely get what the data is you want retourned. can you give a few examples please?

taking a long shot here :

SELECT "ARInvoices"."arpARInvoiceID",
"ARInvoiceLines"."arlSalesOrderID",
"ARInvoices"."arpInvoiceDate",
"ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines"."arlCustomerPO",
"ARInvoices"."arpCustomerOrganizationID",
"ARInvoices"."arpShipLocationID",
"OrganizationLocations"."cmlLocationID",
"OrganizationLocations"."cmlState",
"ARInvoices"."arpInvoiceTotalForeign",
"ARInvoices"."arpInvoiceTaxAmountForeign",
"OrganizationLocations"."cmlCountry"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30')) or ("ARInvoices"."UARPDEPOSITORFINAL" = 1.00
AND "ARInvoices"."arpInvoiceDate" < ('2007-07-26') And ARInvoiceLines"."arlCustomerPO not in
(SELECT "ARInvoiceLines"."arlCustomerPO"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30'))
)

ok, i see at least 1 typo there, and i prob. miss a few parenthesis as well ;)

im sorry, i dont completely get what the data is you want retourned. can you give a few examples please?

taking a long shot here :

SELECT "ARInvoices"."arpARInvoiceID",
"ARInvoiceLines"."arlSalesOrderID",
"ARInvoices"."arpInvoiceDate",
"ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines"."arlCustomerPO",
"ARInvoices"."arpCustomerOrganizationID",
"ARInvoices"."arpShipLocationID",
"OrganizationLocations"."cmlLocationID",
"OrganizationLocations"."cmlState",
"ARInvoices"."arpInvoiceTotalForeign",
"ARInvoices"."arpInvoiceTaxAmountForeign",
"OrganizationLocations"."cmlCountry"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30')) or ("ARInvoices"."UARPDEPOSITORFINAL" = 1.00
AND "ARInvoices"."arpInvoiceDate" < ('2007-07-26') And ARInvoiceLines"."arlCustomerPO" not in
(SELECT "ARInvoiceLines"."arlCustomerPO"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30'))
)

> im sorry, i dont completely get what the data is you want retourned. can you give a few examples
> please?
Sure thing, I probably didn't explain well as this one stumps me.

Data Example:

Invoice Id      Sales ID      CustomerPO    Date          Deposit or final
11169      7509801      PR070514        6/11/07      2
11139      7509801      PR070514        5/21/07      1
11198      630040103      22727680         6/29/07      2
10618      630040103      22727680         10/13/06      1

Ok,
Customer (boss) wants to see all invoices (Finals & Deposits) in one report.
"ARInvoices"."UARPDEPOSITORFINAL" = 1.00 ( = Deposit inv)
"ARInvoices"."UARPDEPOSITORFINAL" = 2.00 (= Final inv)

By montly date ranges on Final:
"ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30')
Where "ARInvoices"."UARPDEPOSITORFINAL" = 2.00

Then pull in all Deposit invoices with the same Customer PO in the date range above or older:
"ARInvoiceLines"."arlCustomerPO" = "ARInvoiceLines"."arlCustomerPO" from first query.
I tried some aliases here but that didn't work.
Customer PO is always the same for both final & deposits entries.

Or better put:
All final invoices by date range, and all deposits that have the SAME customer PO regardless of date.
He would like to see all DEPOSITS on the finals even if they are out of the date range.
Since deposits are always entered before finals they will have the same date or older.

Clear as mud?

James

ohh then my long shot wasnt far of then

but err i am doing this in notepad so i prob have a typo or 2

what i tried to do is expand the second query to check for salesid that are also part of the first query. i dont know if i have the fieldname/tables right; see if you can get it to work :)

SELECT "ARInvoices"."arpARInvoiceID",
"ARInvoiceLines"."arlSalesOrderID",
"ARInvoices"."arpInvoiceDate",
"ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines"."arlCustomerPO",
"ARInvoices"."arpCustomerOrganizationID",
"ARInvoices"."arpShipLocationID",
"OrganizationLocations"."cmlLocationID",
"OrganizationLocations"."cmlState",
"ARInvoices"."arpInvoiceTotalForeign",
"ARInvoices"."arpInvoiceTaxAmountForeign",
"OrganizationLocations"."cmlCountry"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30')) or ("ARInvoices"."UARPDEPOSITORFINAL" = 1.00
AND "ARInvoices"."arpInvoiceDate" < ('2007-07-26') And ARInvoiceLines"."SalesId" in
(SELECT "ARInvoiceLines"."SalesId"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00
AND "ARInvoices"."arpInvoiceDate" between ('2007-07-01') and ('2007-07-30'))
)

That was real close, about 99.5 % there.
A few typo's (i fixed) but dang good for on the fly notepadding... ;)
Still got  some Inv/Customer PO's < end date that do not have a final in the date range.
We only want to see deposits that have a final in the date range.

So if no final in date range do not display deposit.
But display a deposit in date range.

Make since?

James

can you give an example of data that you found that shouldnt be there? the logic in the query would make it almost impossible to return the wrong data so i prob. dont have a proper view of your tabledefinition

Column names in order at top, data below.
None of these have a final in the date range.
Nor are any of these deposits in the date range specified.
But they still displayed and should not.
Some have a null/blank for sales order that is why no data is displayed in that column on some.
(old invoice history) But doesn't affect the query.
These are the first records that are returned before the good data.
There were a few more after these but I thought this might be enough.
If not let me know and I will try to post a snapshot of good and bad mixed.

BTW thanks for your awesome help!!!

Data Example:

ARInvoices."arpARInvoiceID", ARInvoiceLines."arlSalesOrderID", ARInvoices."arpInvoiceDate", "ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines"."arlCustomerPO"

10006                      3/14/06      1      100505-80                              
10013                      3/14/06      1      1504171
10016                      3/14/06      1      543622
10017                      3/14/06      1      102866
10028                      3/14/06      1      207338
10038                      3/14/06      1      991483773
10041                      3/14/06      1      535315
10044                      3/14/06      1      991496754
10045                      3/14/06      1      991437932
10046                      3/14/06      1      991495072
10048                      3/15/06      1      PO25843                                
10050                      3/15/06      1      22287406
10052                      3/15/06      1      22162185
10054                      3/15/06      1      22405240
10066                      3/15/06      1      79376
10071                      3/15/06      1      995157
10074                      3/15/06      1      691411
10080                      3/15/06      1      29071
10091                      3/15/06      1      P34501                                  
10100                      3/16/06      1      991413479
10106                      3/21/06      1      6034751
10127                      3/23/06      1      G106-02                                
10137                      3/24/06      1      991557096
10141                      3/27/06      1      79376
10159                      3/29/06      1      4500081235
10166                      3/30/06      1      102866
10214                      4/19/06      1      1375225
10285                      5/17/06      1                                             
10458                      8/11/06      1                                             
10618      630040103      10/13/06      1      22727680
10686      6514001      11/20/06      1      611075
10795                      1/8/07      1      6066304
10864                      2/6/07      1      22897161
10926                      3/2/07      1      MAC 001-2007                            
10932      7305002      3/5/07      1      2339
10951                      3/13/07      1      1243073
10952      7505201      3/13/07      1      712602 & 712638                        
10980      6323901      3/26/07      1      22983196
10981      6324001      3/26/07      1      22981970
11004      7307601      3/31/07      1      032907B                                
11007      7507902      4/3/07      1      PO-07154                                
11008      7304201      4/5/07      1      23004854
11009      7304301      4/5/07      1      23004929
11034      7102803      4/18/07      1      SMT/DAM/CG/02/APR/2007-08              
11049      7504502      4/23/07      1      2607284
11051      7500302      4/23/07      1      2608015
11139      7509801      5/21/07      1      PR070514                                

since we check by salesid, can the lines that are returned be fawlty dataentry? the wrong salesid at a diff. company?

no prob, i enjoy puzzles like this

That worked better. Here is the final SQL I used.
I still need to clean a little as I get a few records duped but can't get DISTINCT in the subquery to work where it dupes the Customer PO.
Was only a couple of records that did this so I am looking at the data that was entered.

SELECT DISTINCT "ARInvoices"."arpARInvoiceID",
"ARInvoiceLines_QRY1"."arlSalesOrderID",
"ARInvoices"."arpInvoiceDate",
"ARInvoices"."UARPDEPOSITORFINAL",
"ARInvoiceLines_QRY1"."arlCustomerPO",
"ARInvoices"."arpCustomerOrganizationID",
"ARInvoices"."arpShipLocationID",
"OrganizationLocations"."cmlLocationID",
"OrganizationLocations"."cmlState",
"ARInvoices"."arpInvoiceTotalForeign",
"ARInvoices"."arpInvoiceTaxAmountForeign",
"OrganizationLocations"."cmlCountry"
FROM   ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines_QRY1"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines_QRY1"."arlARInvoiceID"
Where ("ARInvoices"."UARPDEPOSITORFINAL" = 2.00 AND "ARInvoices"."arpInvoiceDate" between ('2007-06-01') and ('2007-06-30'))
OR "ARInvoices"."UARPDEPOSITORFINAL" = 1.00 AND "ARInvoiceLines_QRY1"."arlSalesOrderID"
IN
(SELECT "ARInvoiceLines"."arlSalesOrderID"
FROM ("M1_MS"."dbo"."OrganizationLocations" "OrganizationLocations"
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoices" "ARInvoices"
ON ("OrganizationLocations"."cmlLocationID"="ARInvoices"."arpShipLocationID")
AND ("OrganizationLocations"."cmlOrganizationID"="ARInvoices"."arpCustomerOrganizationID"))
LEFT OUTER JOIN "M1_MS"."dbo"."ARInvoiceLines" "ARInvoiceLines"
ON "ARInvoices"."arpARInvoiceID"="ARInvoiceLines"."arlARInvoiceID"
Where
("ARInvoices"."UARPDEPOSITORFINAL" = 2.00 AND "ARInvoiceLines_QRY1"."arlCustomerPO"="ARInvoiceLines"."arlCustomerPO"
AND "ARInvoices"."arpInvoiceDate" between ('2007-06-01') and ('2007-06-30'))
)

Here is a duped record.

10952  7505201  3/13/07      Deposit  712602 & 712638  CLEARSTRM $25,500.00  $-  Ireland  
11212                  6/30/07  Final 712602 & 712638  CLEARSTRM   $38,782.68        $-  Ireland                
11212  7505201  6/30/07  Final 712602 & 712638  CLEARSTRM   $38,782.68        $-  Ireland            

I guess I could add a statment not to bring in recodrs that have a null or blank for SaleOrderID.?.
Looks like I am 99.9% there.

J         

ok glad it was solved. good luck with the last .1 % ;)

Ed.

oh missed your last question. Sure, add

and not(SaleOrderId is null)