Home > IT Answers > AS/400 > AS400 Query Join
lorit
365 pts.
 AS400 Query Join
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?



Software/Hardware used:
ASKED: December 30, 2009  5:45 PM
UPDATED: December 31, 2009  9:03 AM
RELATED QUESTIONS
Answer Wiki:
Last Wiki Answer Submitted:  Be the first to answer this question.
All Answer Wiki Contributors:  Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

  
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

TomLiotta
 108,360 pts.