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 to share