Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Joining to different queries Post Reply Post New Topic
Author Message
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Topic: Joining to different queries
    Posted: 06 Jan 2012 at 9:36am

I need help

I have two different queries one Invoice and one Payment. I want to be able to join the queries together and get one report. I need the report to show me the client and what company the invoice was bill to and whatever payment for that client from any of the companies we have. Here is a sample of the querie I have written for invoice and payment.

 

SELECT "Invoices"."ClientID", "Invoices"."CompanyID", "Invoices"."InvoiceNo", "Invoices"."DateCreated", "InvoiceDetails"."Amount", "Invoices"."Type"

 FROM   "PHCS"."dbo"."Invoices" "Invoices" INNER JOIN "PHCS"."dbo"."InvoiceDetails" "InvoiceDetails" ON "Invoices"."InvoiceNo"="InvoiceDetails"."InvoiceNo"

 WHERE  ("Invoices"."ClientID"='ABCD02' OR "Invoices"."ClientID"='ABSWC01' OR "Invoices"."ClientID"='ACAUP01' OR "Invoices"."ClientID"='ACDP01' OR "Invoices"."ClientID"='ACDPC01' OR "Invoices"."ClientID"='ACHLP01' OR "Invoices"."ClientID"='ACSAM01' OR "Invoices"."ClientID"='ADAPT01')

 ORDER BY "Invoices"."CompanyID", "Invoices"."InvoiceNo"

 

SELECT "Payments"."ClientID", "Payments"."CompanyID", "PaymentDetails"."PaymentNo", "PaymentDetails"."DateCreated", "Payments"."Type", "PaymentDetails"."Amount", "PaymentDetails"."InvoiceNo", "Payments"."Reference"

 FROM   "PHCS"."dbo"."Payments" "Payments" INNER JOIN "PHCS"."dbo"."PaymentDetails" "PaymentDetails" ON "Payments"."PaymentNo"="PaymentDetails"."PaymentNo"

 WHERE  ("Payments"."ClientID"='A PHILL01' OR "Payments"."ClientID"='ABBRA01' OR "Payments"."ClientID"='ABCD02' OR "Payments"."ClientID"='ABSWC01' OR "Payments"."ClientID"='ACAUP01')

ORDER BY "Payments"."CompanyID", "PaymentDetails"."PaymentNo"

 

 

This is how I would like the report to look to group the Inv# together from Payment and Invoice

 

Client  Company1  Inv#     Date  Amt  Paymt#    Paymt type    Inv#   Date        Amt

ABCD02   ABC   100    1/1/11 250.00

ABCD02   EFG                                    2356         CH              100  2/10/11      250.00

 

 

Any help I would appreciate it.

 

Thanks

IP IP Logged
sgtjim
Newbie
Newbie
Avatar

Joined: 23 Aug 2011
Online Status: Offline
Posts: 32
Quote sgtjim Replybullet Posted: 06 Jan 2012 at 10:49am
You can either use UNION or UNION ALL. UNION will only return rows that are in both queries, I don't use this very much. UNION ALL will return all the rows from both queries regardless if one row is in the other query or not.

The code will look something like this

SELECT
.............
FROM
........
WHERE
.............

UNION ALL

SELECT
.............
FROM
........
WHERE
.............


The kicker is that both selected statements must have the same number of fields selected of the same data type. Now, if one query does not have all the fields as the other you can put a dummy field to hold it.

Like So;

NULL As Field_Name in your select statement

Also your order by will have to put at the end of the last query and it must be a field that both queries share.
IP IP Logged
kbnyny
Newbie
Newbie


Joined: 05 Aug 2010
Location: United States
Online Status: Offline
Posts: 14
Quote kbnyny Replybullet Posted: 06 Jan 2012 at 11:10am
Thank you very much
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.