[FIXED] What is the best way to save query results with a subquery?

Issue

I want to show all the pairs of similar products liked together by users so I thought of creating a VIEW in order to update the table dynamically.

I get the message that I can not CREATE A VIEW with a subquery. I also thought of creating a temporary table but it's not effective.

How can I save my query results in a table when there is a VIEW limitation?

This is my query:

SELECT DISTINCT c.item1
           , c.item2
           , count(*) as times_liked_together 
         FROM 
     ( SELECT a.product_id as item1
            , b.product_id as item2 
    FROM items a 
    join items b 
         ON a.wishlist_id = b.wishlist_id 
         AND a.product_id != b.product_id
     ) c 
 GROUP 
      BY c.item1
     , c.item2

Solution

MySQL has this limitation on views. In your case, you don't need a subquery:

SELECT i1.product_id as item1, i2.product_id as liked_with,
       COUNT(*) as num_liked_together
FROM items i1 INNER JOIN
     items i2
     ON i1.wishlist_id = i2.wishlist_id AND
        i1.product_id < i2.product_id
GROUP BY item1, item2;

Note that I changed the <> to <. I don't see a reason to have both (a, b) and (b, a) in the result set. If you do, change the < back to <>.

Also, SELECT DISTINCT is almost never used with GROUP BY.

Answered By - Gordon Linoff