Displaying one column from inner query

Question

Please help me solve this:

SELECT a.prs_code,
       a.cc,
       b.description
FROM   idp_inpadoc_prs_cc a,
       idp_inpadoc_cat_desc b
WHERE  a.ID = b.ID
       AND a.prs_code IN (SELECT prs_code
                          FROM   tls221_inpadoc_prs c,
                                 tls201_appln d
                          WHERE  c.appln_id = d.appln_id
                                 AND c.appln_id IN ( '1', '2' )); 

In this query, along with prs_code, cc, description, I also want to display the corresponding appln_id. How can I do this? Kindly help. Thanks! :)

Answer 1

so when you start looking at transforming this to joins this should do the same as your query because appln_id is on both the tls221_inadoc_prs and the tsl201_appln tables so you don't actually need the later table if the tls221_inadoc_prs.appln_id is a foreign key to tls201_appln.appln_id meaning that if the value is in tls221_inadoc_prs then it also must be in tls201_appln

SELECT a.prs_code,
       a.cc,
       b.description
      ,c.appln_id
FROM
    idp_inpadoc_prs_cc a
    INNER JOIN idp_inpadoc_cat_desc b
    ON a.ID = b.ID
    INNER JOIN tls221_inpadoc_prs c
    ON a.prs_code = c.prs_code
    AND d.appln_id IN (1,2)

If it is not a foreign key you can simply add another join as well:

SELECT a.prs_code,
       a.cc,
       b.description
      ,c.appln_id
FROM
    idp_inpadoc_prs_cc a
    INNER JOIN idp_inpadoc_cat_desc b
    ON a.ID = b.ID
    INNER JOIN tls221_inpadoc_prs c
    ON a.prs_code = c.prs_code
    AND c.appln_id IN ( '1', '2' )
    INNER JOIN tls201_appln d
    ON c.appln_id = d.appln_id