Performing join on 3 tables & retrieving selected columns I need to write a query which fetches data from 3 tables &amp; retrieve selected columns out of it.
The query below works fine. I am interested to include 1 more column in my SELECT statement which is available in 'PI_PUBLICATION' table.
Since I am using multiple column subquery here, can you please help me out?

SELECT pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET, MESSAGE_XML
FROM PI_RESUBMIT_HIERARCHY_DETAIL pirhd
WHERE (pirhd.GTIN, pirhd.INFORMATION_PROVIDER, pirhd.RETAILER, pirhd.TARGET_MARKET) 
IN (
SELECT item.GTIN, item.INFORMATION_PROVIDER, item.RETAILER, item.TARGET_MARKET 
FROM Pi_Item Item 
WHERE item.GTIN = pirhd.GTIN and item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER AND item.TARGET_MARKET = pirhd.TARGET_MARKET 
AND item.RETAILER = pirhd.RETAILER 
AND (item.obj_id IN (SELECT PUB.OBJ_ID from PI_PUBLICATION pub)) 
) AND RESUBMIT_ITEM_DETAIL_ID='88956457' ORDER BY pirhd.GTIN;
You could try the following restructured query which uses joins instead of the sub-selects;

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN


Another couple of possibilities;

WITH bi_pub AS
(SELECT DISTINCT obj_id , another_column
   FROM PI_PUBLICATION )
SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN BI_PUB pub
          ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN


OR

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       (SELECT pub.another_column
          FROM PI_PUBLICATION pub
         WHERE pub.obj_id = item.obj_id
           AND ROWNUM = 1)
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN


This really depends on your data and requirements and only you can answer that.