I am trying to create a summary table from two physical tables. I can't use
join between those two tables because I don't have one(Many) to one relation between them.


Table Name: FIELD_MEMBER_ACCT_RELATION

FMID BIGINT
ACCT_NBR VARCHAR
OFF_NBR CHAR
RR_NBR CHAR
RELATED_PARTY CHAR
ACCT_CATEGORY INT

Primary Key(FMID,ACCT_NBR)

Table Name: TARGET_ACCT_RELATION

ACCT_NBR VARCHAR
TARGET_ID BIGINT
STATUS SHORT
INDICATOR SHORT

Primary Key(ACCT_NBR)

Note:
A Given ACCT_NBR of FIELD_MEMBER_ACCT_RELATION may or may not exists in TARGET_ACCT_RELATION table.
But Still I want that record as part of summary table.

How can I create a MQT(Summary Table) with following
information

COLUMN(Table Name)

FMID(FIELD_MEMBER_ACCT_RELATION)
ACCT_NBR(FIELD_MEMBER_ACCT_RELATION)
OFF_NBR(FIELD_MEMBER_ACCT_RELATION)
RR_NBR(FIELD_MEMBER_ACCT_RELATION)
RELATED_PARTY(FIELD_MEMBER_ACCT_RELATION)
ACCT_CATEGORY(FIELD_MEMBER_ACCT_RELATION)
TARGET_ID(TARGET_ACCT_RELATION)


In the summary table, I want the data like this

a. Take ACCT_NBR from FIELD_MEMBER_ACCT_RELATION.
If the ACCT_NBR exists in TARGET_ACCT_RELATION then take
TARGET_ID from TARGET_ACCT_RELATION.
b. If the ACCT_NBR does not exists in TARGET_ACCT_RELATION then put
some dummy value(-999) as TARGET_ID


Let me know if additional information is required.


Thanks
Shri

You can join them, just use LEFT OUTER JOIN. This will give all qualifying rows in the "left" table even if there is not any dependant rows in the "right" table. I suggest that you read the SQL manual.

Andy

Thanks Winner. I had gone thru the SQL documentation and prepared the following query.


SELECT FM.FMID, FM.ACCT_NBR, FM.OFF_NBR, FM.RR_NBR, FM.RELATED_PARTY, FM.ACCT_CATEGORY, TA.TARGET_ID
FROM FIELD_MEMBER_ACCT_RELATION FM LEFT JOIN TARET_MASTER ON FM.ACCT_NBR= TM.ACCT_NBR AND FM.ACCT_CATEGORY=3


FIELD_MEMBER_ACCT_RELATION contains 2Million records while as TARGET contains 800,000 records. I need your suggestion about the approach.

1****n this sql in production for each request
2.Create a summary table and access this information from summary table.

Thanks
Shri

Thanks Winner. I had gone thru the SQL documentation and prepared the following query.


SELECT FM.FMID, FM.ACCT_NBR, FM.OFF_NBR, FM.RR_NBR, FM.RELATED_PARTY, FM.ACCT_CATEGORY, TA.TARGET_ID
FROM FIELD_MEMBER_ACCT_RELATION FM LEFT JOIN TARET_MASTER ON FM.ACCT_NBR= TM.ACCT_NBR AND FM.ACCT_CATEGORY=3


FIELD_MEMBER_ACCT_RELATION contains 2Million records while as TARGET contains 800,000 records. I need your suggestion about the approach.

1****n this sql in production for each request
2.Create a summary table and access this information from summary table.

Thanks
Shri

Will you be running the same query every time ? or will you be saying 'where account number = xyz and fmid=abc " where xyz and abc vary each time ?

How frequently are these tables updated or new rows inserted ?

Sathyaram

Will you be running the same query every time ? or will you be saying 'where account number = xyz and fmid=abc " where xyz and abc vary each time ?

<Shri> Parameter value changes for each request

How frequently are these tables updated or new rows inserted ?
<Shri>Changes takes place in two ways
1. During Market Hours(9:30 ET to 4:00 PM ET) changes will be rare.
2. At 12:00 , one of the base table will get dropped and recreated with new data.

Note: I am not sure I can access summary table by dropping the underlying physical table?