Outer Join

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.

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

Processing your response...

Discuss This Question: 1 &nbspReply

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

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

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

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

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

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