Outer Join

DASully
pts.
Tags:
IBM DB2
I have two queries below. One table has one row for each the other table has many rows for each one in the first table. Can someone show me how I can join these tables and get the results I'm looking for? SELECT A.NAME AS TABLE_NAME , ',', B.NAME AS TBL_SPACE, ',', B.DBNAME AS DB_NAME, ',', B.BPOOL AS TBL_POOL, ',', C.STORNAME AS TBL_STOG, ',' FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLESPACE B, SYSIBM.SYSTABLEPART C WHERE A.TSNAME = B.NAME AND A.DBNAME = B.DBNAME AND C.TSNAME = A.TSNAME AND C.DBNAME = A.DBNAME AND A.DBNAME NOT LIKE 'DSN%' AND A.DBNAME NOT LIKE 'PTDB%' Query two: SELECT DISTINCT A.NAME , A.TBNAME, B.STORNAME, A.BPOOL FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSINDEXPART B WHERE A.NAME = B.IXNAME AND A.DBNAME NOT LIKE 'BMC%' AND A.DBNAME NOT LIKE 'DSN%' AND A.DBNAME NOT LIKE 'PTDB%' ORDER BY A.BPOOL, A.NAME Thanks so much.
Asked: Last updated:
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.

take a look at this expert’s article

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054418,00.html

I am guessing from your question you have a 1:M relationship that needs to be queried. the KB article does a good job of explaining it.

good luck!
ronze

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.
  • Pgurev
    I am not sure what you are looking for with these 2 SQLs. If in your enviroment you don't have partitioned table spaces you are fine, but if you do have them, then the first SQL would bring multiple rows per table. Assuming you don't have partitioned TSs, then the following SQL could be a starting point for you: SELECT A.NAME AS TABLE_NAME ,',', B.NAME AS TBL_SPACE,',', B.DBNAME AS DB_NAME,',', B.BPOOL AS TBL_POOL,',', C.STORNAME AS TBL_STOG,',', IX.NAME , IX.TBNAME, IX.STORNAME, IX.BPOOL FROM SYSIBM.SYSTABLES A, SYSIBM.SYSTABLESPACE B, SYSIBM.SYSTABLEPART C INNER JOIN (SELECT DISTINCT A.NAME , A.TBNAME, B.STORNAME, A.BPOOL, A.TBCREATOR FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSINDEXPART B WHERE A.NAME = B.IXNAME AND A.DBNAME NOT LIKE 'BMC%' AND A.DBNAME NOT LIKE 'DSN%' AND A.DBNAME NOT LIKE 'PTDB%') IX ON A.NAME = IX.TBNAME AND A.CREATOR = IX.TBCREATOR WHERE A.TSNAME = B.NAME AND A.DBNAME = B.DBNAME AND C.TSNAME = A.TSNAME AND C.DBNAME = A.DBNAME AND A.DBNAME NOT LIKE 'DSN%' AND A.DBNAME NOT LIKE 'PTDB%' ORDER BY IX.BPOOL, IX.NAME Hope this helps regards Paulo
    0 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.

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

Following

Share this item with your network: