Eric S. Eric S. - 1 month ago 6
SQL Question

trying to merge 2 views

I need to retrieve the data provided by this view :
BLICK_1_DESCR_LIST.

I didn't find how to create it directly. So I created the view BLICK_1_DESCR_NO_LIST which is used in the second view BLICK_1_DESCR_LIST.

I would like to do it in one view which is better.

CREATE VIEW BLICK_1_DESCR_NO_LIST
AS SELECT ITEM_ID , MIN(ITEM_DESCR_NO) MIN_I_D_NO,
COUNT(ITEM_DESCR_NO) COUNT_I_D_NO FROM BLICK_ITEM_DESCR
GROUP BY ITEM_ID
UNION
SELECT ID , 0 ZERO, 0 ZERO2 FROM BLICK_ITEM
LEFT JOIN BLICK_ITEM_DESCR ON BLICK_ITEM.ID = BLICK_ITEM_DESCR.ITEM_ID
WHERE ITEM_DESCR_NO IS NULL;


CREATE VIEW BLICK_1_DESCR_LIST
AS SELECT V1.ITEM_ID, V1.MIN_I_D_NO, V1.COUNT_I_D_NO, T1.ITEM_DESCR
FROM BLICK_1_DESCR_NO_LIST V1
LEFT JOIN BLICK_ITEM_DESCR T1 ON V1.ITEM_ID = T1.ITEM_ID
AND V1.MIN_I_D_NO = T1.ITEM_DESCR_NO
ORDER BY ITEM_ID;

Answer

You can just incorporate the first view as a subquery:

CREATE VIEW BLICK_1_DESCR_LIST AS
    SELECT V1.ITEM_ID, V1.MIN_I_D_NO, V1.COUNT_I_D_NO, T1.ITEM_DESCR
    FROM ((SELECT ITEM_ID, MIN(ITEM_DESCR_NO) as MIN_I_D_NO, 
COUNT(ITEM_DESCR_NO) as COUNT_I_D_NO
           FROM BLICK_ITEM_DESCR
           GROUP BY ITEM_ID
          ) UNION
          (SELECT ID, 0, 0
           FROM BLICK_ITEM LEFT JOIN
                BLICK_ITEM_DESCR
                ON BLICK_ITEM.ID = BLICK_ITEM_DESCR.ITEM_ID
           WHERE ITEM_DESCR_NO IS NULL
          )) V1 LEFT JOIN
         BLICK_ITEM_DESCR T1 
         ON V1.ITEM_ID = T1.ITEM_ID AND
            V1.MIN_I_D_NO = T1.ITEM_DESCR_NO
    ORDER BY ITEM_ID;