Combine two queries into one using UNION or any other solutions

Request 1 for user visit, type / dislike or other activity history: FIDDLE

Query 2 for all product records: FIDDLE

The first query will give this result for that specific user:

NAME    MODEL   ISBOOKED    USER_MIN    USER_MAX    USER_LIKE_TYPE
Tom     TOYA    1           100         300         0 
Tom     TOYB    1           (null)      (null)      1
Tom     ToyC    (null)      (null)      (null)      (null)

      

The second request will provide records of all products

MODEL   RESALE_USERSNO  AVG_MIN AVG_MAX LIKES   DISLIKES
ToyA    1               100     300     1       0 
ToyB    0               (null)  (null)  1       1
ToyC    1               50      400     (null)  (null)

      

Is it possible to combine user record and shared product records into one of the following:

MODEL RESALE_USERSNO AVG_MIN AVG_MAX LIKES DISLIKES NAME ISBOOKED USER_MIN USER_MAX USER_LIKE_TYPE
ToyA    1            100     300     1      0       Tom   1        100       300       0 
ToyB    0            (null) (null)   1      1       Tom   1        (null)    (null)    1
ToyC    1            50      400     (null) (null)  Tom   (null)    (null)    (null)   (null)

      

Here is my attempt at ( FIDDLE ) using UNION but doesn't seem to work

Request 1

SELECT a.NAME,b.MODEL,c.ISBOOKED,d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,e.LIKE_TYPE AS USER_LIKE_TYPE
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'

      

Request 2

SELECT b.MODEL,COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX, 
        b.MODEL_ID, a.LIKES, a.DISLIKES
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER

      

Unable to try:

SELECT b.MODEL,
       NULL AS MODEL_ID,
       a.NAME,
       NULL AS RESALE_USERSNO,
       NULL AS AVG_MIN,
       NULL AS AVG_MAX,
       NULL AS LIKES,
       NULL AS DISLIKES,
       d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,
       e.LIKE_TYPE AS USER_LIKE_TYPE,
       c.ISBOOKED
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'

UNION

SELECT  b.MODEL,
        b.MODEL_ID,
        NULL AS NAME,
        COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX,
        a.LIKES, 
        a.DISLIKES,
        NULL AS ISBOOKED,
        NULL AS USER_MIN,
        NULL AS USER_MAX,
        NULL AS USER_LIKE_TYPE
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER

      

+3


source to share


1 answer


Use Join

instead union

. Treat the 2 results as separate inner tabs and attach them to the model code to get the desired result.

The spell is here: http://sqlfiddle.com/#!2/6bfcb4/11



SELECT * FROM 
(
SELECT a.NAME,b.MODEL,c.ISBOOKED,d.MIN_ AS USER_MIN,
       d.MAX_ AS USER_MAX,e.LIKE_TYPE AS USER_LIKE_TYPE
     FROM `USER_ID` a
     JOIN `MODEL_ID` b
       ON b.MODEL_NUMBER IN ('AAA','BBB','CCC')
LEFT JOIN `BOOKMARK` c 
       ON c.USER_ID = a.USER_ID
      AND c.MODEL_ID = b.MODEL_ID         
LEFT JOIN `RESALE_POLL` d 
       ON d.USER_ID = a.USER_ID
      AND d.MODEL_ID = b.MODEL_ID 
LEFT JOIN `LIKE_DISLIKE` e
       ON e.USER_ID = a.USER_ID
      AND e.MODEL_ID = b.MODEL_ID
    WHERE a.FACEBOOK_ID = 'FB1111'
)TAB1

JOIN

(SELECT b.MODEL,COUNT( c.USER_ID ) AS RESALE_USERSNO, 
        ROUND( AVG( c.MIN_ ) , 1 ) AS AVG_MIN, 
        ROUND( AVG( c.MAX_ ) , 1 ) AS AVG_MAX, 
        b.MODEL_ID, a.LIKES, a.DISLIKES
        FROM  `LIKES_TOTAL` a
        RIGHT JOIN  `MODEL_ID` b 
          ON a.MODEL_ID = b.MODEL_ID
        LEFT JOIN  `RESALE_POLL` c 
          ON c.MODEL_ID = b.MODEL_ID
        WHERE b.MODEL_NUMBER IN ('AAA','BBB','CCC')
        GROUP BY b.MODEL_NUMBER
)TAB2

ON TAB1.MODEL = TAB2.MODEL

      

+1


source







All Articles