AS400 Query Join

365 pts.
Tags:
AS/400 Query
JOIN
JOIN statement
I need expert help. I need to create a query that will use four tables. Each table has a column of data that I need for the report. The end result should display the following: Name, City, Amount, Reason. 

Here is my first query that gives me the name and city:

SELECT "T16.GNAM".N_NAME, "T16.GNAM".N_CITY FROM QS36F."T16.GNAM" AS "T16.GNAM" Join QS36F."T16.CPRP" on QS36F."T16.CPRP".CP_PRIMARY_TAXPAYER = "T16.GNAM".N_ID_NR ;

Here is my second query that gives me the amount:

 SELECT "T16.CADJ".AD_TOTAL_ADJUSTMENT FROM QS36F."T16.CADJ" AS "T16.CADJ" WHERE "T16.CADJ".AD_ADJUSTMENT_DATE > 20091031 AND "T16.CADJ".AD_ADJUSTMENT_DATE < 20091201 AND "T16.CADJ".AD_TOTAL_ADJUSTMENT > 100 and "T16.CADJ".AD_REASON IN ('R', 'C');

This gives me the reason:

SELECT "T16.CRDC".RD_COMMENT FROM QS36F."T16.CRDC" AS "T16.CRDC" Join QS36F."T16.CADJ" on QS36F."T16.CADJ".AD_TAXBILL_NO = "T16.CRDC".RD_TAXBILL_NO WHERE QS36F."T16.CADJ".AD_ADJUSTMENT_DATE > 20091031 AND QS36F."T16.CADJ".AD_ADJUSTMENT_DATE < 20091201 AND QS36F."T16.CADJ".AD_TOTAL_ADJUSTMENT > 100 and QS36F."T16.CADJ".AD_REASON IN ('R', 'C');

My question is how do I combine this into one query to generate Name, City, Amount, Reason all in one report?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    SELECT
      "T16.CADJ".AD_TOTAL_ADJUSTMENT
     FROM
      QS36F."T16.CADJ" AS "T16.CADJ"
     WHERE
      "T16.CADJ".AD_ADJUSTMENT_DATE > 20091031 AND
      "T16.CADJ".AD_ADJUSTMENT_DATE < 20091201 AND
      "T16.CADJ".AD_TOTAL_ADJUSTMENT > 100 and
      "T16.CADJ".AD_REASON IN ('R', 'C')
    That seems to be your second query. It doesn't seem to give the the amount but rather simply a selected subset of amounts. T16.CADJ has a relationship to T16.CRDC through AD_TAXBILL_NO, but nothing in what you show illustrates any relationship to T16.GNAM nor to T16.CPRP. Unless you show us how T16.GNAM or T16.CPRP relates to T16.CADJ or T16.CRDC, we can't guess. I would normally assume that something like taxpayer ID has to relate somehow to T16.CADJ or T16.CRDC, maybe not directly but perhaps even through something else such as a "tax bill ID". Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following