0

I need to write a query which fetches data from 3 tables & 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;
  • Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Mar 21 '17 at 8:53
  • @a_horse_with_no_name: Oracle. – Arjun Mar 21 '17 at 9:09
1

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.

  • Tried this initially. Due to the data records that I have, this query resulted in duplicate records (records being displayed twice). So switched to multiple column subquery. Any other way to achieve this? – Arjun Mar 21 '17 at 9:35
  • So I presume that you can have multiple PI_PUBLICATION records for ID '88956457'. In which you will need to decide which occurrence on that table you want and then limit the join on it using that criteria or just use a DISTINCT in the select clause. – BriteSponge Mar 21 '17 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.