CODEWITHSUNDEEP
sqloracle-databasejoin
sathish palanisamy
sathish palanisamy

Reputation: 1

Join 2 oracle queries

someone, please help to join the below queries.

I have tried my best but not able to join with the condition.

PLN_ID is the common column on both the tables.

Query 1-

SELECT PLN_ID
     , ASSORTMENT_GROUP
     , STORE
     , PLANOGRAM
     , STATUS 
  FROM ACN_PLANOGRAMS 
 WHERE PLANOGRAM not like '%<Untitled>%'
     ;

​ Query 2

    SELECT distinct(PLN_ID)
         , count(*)
         , (sum(WIDTH))      AS width
      FROM ACN_FIXEL 
     WHERE type='0'
  GROUP BY PLN_ID
    HAVING count(*) > 1
         ;

Upvotes: 0

Views: 52

Answers (3)

Kiran Patil
Kiran Patil

Reputation: 339

Please changes join what you want you. Try this query:

    SELECT 
        DISTINCT(a.PLN_ID),
        (SUM(a.WIDTH)) AS width,
        b.PLN_ID,
        b.ASSORTMENT_GROUP,
        b.STORE,
        b.PLANOGRAM,
        b.STATUS 
    FROM 
        ACN_FIXEL a 
    INNER JOIN 
        ACN_PLANOGRAMS b ON a.PLN_ID = b.PLN_ID
    WHERE 
        a.type = '0' 
        AND b.PLANOGRAM NOT LIKE '%<Untitled>%'
    GROUP BY 
        a.PLN_ID,
b.PLN_ID,
        b.ASSORTMENT_GROUP,
        b.STORE,
        b.PLANOGRAM,
        b.STATUS
    HAVING 
        COUNT(*) > 1

Upvotes: 2

APC
APC

Reputation: 146219

There are several ways to solve this. Without understanding your data model or business logic I offer the simplest solution, a derived table (inline view):

SELECT p.PLN_ID
     , p.ASSORTMENT_GROUP
     , p.STORE
     , p.PLANOGRAM
     , p.STATUS 
     , f.fixel_count
     , f.fixel_width
  FROM ACN_PLANOGRAMS p
       inner join (SELECT PLN_ID
                          , count(*) as fixel_count
                          , (sum(WIDTH))      AS fixel_width
                    FROM ACN_FIXEL 
                    WHERE type='0'
                    GROUP BY PLN_ID
                    HAVING count(*) > 1 ) f 
       on f.pln_id = p.pln_id
 WHERE p.PLANOGRAM not like '%<Untitled>%'
     ;

This solution only returns results for PLN_ID in both result sets. If you have a different logic you may need to use LEFT OUTER JOIN instead.

Upvotes: 1

collapsar
collapsar

Reputation: 17238

Make Query 2 a subquery:

    SELECT ap.PLN_ID
         , ap.ASSORTMENT_GROUP
         , ap.STORE
         , ap.PLANOGRAM
         , ap.STATUS
         , sq.cnt
         , sq.width 
      FROM ACN_PLANOGRAMS  ap
      JOIN (
                  SELECT PLN_ID
                       , count(*)          AS cnt
                       , sum(WIDTH)        AS width
                    FROM ACN_FIXEL 
                   WHERE type='0'
                GROUP BY PLN_ID
                  HAVING count(*) > 1
           ) sq
        ON ( sq.PLN_ID = ap.PLN_ID )
     WHERE ap.PLANOGRAM not like '%<Untitled>%'
         ;

Upvotes: 0

Related Questions