patternMinor

Select Count of one table into another

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

intoselectoneanothercounttable

Problem

I have one SQL statement as:

SELECT   ARTICLES.NEWS_ARTCL_ID, ARTICLES.NEWS_ARTCL_TTL_DES,
         ARTICLES.NEWS_ARTCL_CNTNT_T, ARTICLES.NEWS_ARTCL_PUB_DT, 
         ARTICLES.NEWS_ARTCL_AUTH_NM, ARTICLES.NEWS_ARTCL_URL,
         ARTICLES.MEDIA_URL, ARTICLES.ARTCL_SRC_ID, SOURCES.ARTCL_SRC_NM,
         MEDIA.MEDIA_TYPE_DESCRIP
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES,
         RSKLMOBILEB2E.MEDIA_TYPE MEDIA, 
         RSKLMOBILEB2E.ARTICLE_SOURCE SOURCES
WHERE    ARTICLES.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF
AND      ARTICLES.ARTCL_SRC_ID = SOURCES.ARTCL_SRC_ID  
AND      ARTICLES.ARTCL_SRC_ID = 1
ORDER BY ARTICLES.NEWS_ARTCL_PUB_DT;

Now I need to combine another SQL statement into one which is:

SELECT   COUNT(*)
FROM     RSKLMOBILEB2E.NEWS_LIKES LIKES, 
         RSKLMOBILEB2E.NEWS_ARTICLE ARTICLES
WHERE    LIKES.NEWS_ARTCL_ID = ARTICLES.NEWS_ARTCL_ID;

Not sure how I can achieve this as I am new to Oracle. Basically I have one table which contains articles and I need to include the user likes which is in another table.

Any help regarding this is much appreciated.

Solution

Try the following:

SELECT   ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES,
         ART.NEWS_ARTCL_CNTNT_T, ART.NEWS_ARTCL_PUB_DT,
         ART.NEWS_ARTCL_AUTH_NM, ART.NEWS_ARTCL_URL,
         ART.MEDIA_URL, ART.ARTCL_SRC_ID, SRC.ARTCL_SRC_NM,
         MED.MEDIA_TYPE_DESCRIP, LIK.TOTAL
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ART,
         RSKLMOBILEB2E.MEDIA_TYPE MED,
         RSKLMOBILEB2E.ARTICLE_SOURCE SRC,
         (SELECT   AR1.NEWS_ARTCL_ID, COUNT(*) TOTAL
          FROM     RSKLMOBILEB2E.NEWS_ARTICLE AR1,
                   RSKLMOBILEB2E.NEWS_LIKES LK1
          WHERE    AR1.NEWS_ARTCL_ID      = LK1.NEWS_ARTCL_ID) LIK
WHERE    ART.MEDIA_TYPE_IDENTIF = MED.MEDIA_TYPE_IDENTIF
AND      ART.ARTCL_SRC_ID       = SRC.ARTCL_SRC_ID
AND      ART.ARTCL_SRC_ID       = 1
AND      ART.NEWS_ARTCL_ID      = LIK.NEWS_ARTCL_ID
ORDER BY ART.NEWS_ARTCL_PUB_DT;

Code Snippets

SELECT   ART.NEWS_ARTCL_ID, ART.NEWS_ARTCL_TTL_DES,
         ART.NEWS_ARTCL_CNTNT_T, ART.NEWS_ARTCL_PUB_DT,
         ART.NEWS_ARTCL_AUTH_NM, ART.NEWS_ARTCL_URL,
         ART.MEDIA_URL, ART.ARTCL_SRC_ID, SRC.ARTCL_SRC_NM,
         MED.MEDIA_TYPE_DESCRIP, LIK.TOTAL
FROM     RSKLMOBILEB2E.NEWS_ARTICLE ART,
         RSKLMOBILEB2E.MEDIA_TYPE MED,
         RSKLMOBILEB2E.ARTICLE_SOURCE SRC,
         (SELECT   AR1.NEWS_ARTCL_ID, COUNT(*) TOTAL
          FROM     RSKLMOBILEB2E.NEWS_ARTICLE AR1,
                   RSKLMOBILEB2E.NEWS_LIKES LK1
          WHERE    AR1.NEWS_ARTCL_ID      = LK1.NEWS_ARTCL_ID) LIK
WHERE    ART.MEDIA_TYPE_IDENTIF = MED.MEDIA_TYPE_IDENTIF
AND      ART.ARTCL_SRC_ID       = SRC.ARTCL_SRC_ID
AND      ART.ARTCL_SRC_ID       = 1
AND      ART.NEWS_ARTCL_ID      = LIK.NEWS_ARTCL_ID
ORDER BY ART.NEWS_ARTCL_PUB_DT;

Context

StackExchange Database Administrators Q#111315, answer score: 2

Revisions (0)

No revisions yet.