doudaochu1699 2016-02-01 19:42
浏览 91
已采纳

MYSQL - 加入或子查询问题

I am trying to get a query to return all the relevant records for products which feature in my featured projects table. All is working for the simple MYSQL query however I want to get the Thumb Image for each product two which is stored in another table (product_image) but with a catch. In this "product_image" table not every products (product_id) will have a record as it is only populated on upload of images and some products may have several records (images stored) I only want one record returned for each product ID. So a product record in the products table has a 0 to many relationship with the product_image table.

I have a working basic SQL statement for the basics without returning the image, I can code a query that only returns the products who have records in the product image table but I need the query to return all the rows in the outer query and then 1 thumb_img that matches the product id from the image table and if there is no images it can return a Null value.

Is this possible? Below is my latest attempt but this just returns the matching thumb for the first product_ID and duplicates this for the other 3(in this case due to limit at end) records. I need the matching thumb for each record rather then the matching one for the first record repeated between the rest. And if there is no records for that product ID a null returned.

SELECT * FROM `products`, `featured_products`, `shop`, (SELECT thumbSrc
          FROM product_image
         WHERE products.productId = product_image.productID
        ORDER BY product_image.position ASC
         LIMIT 1) image
where shop.shopId = products.shopId AND featured_products.productId = products.productId AND visible = '1' LIMIT 4

Thanks in advance for any help / feedback. Is it possible to do this as I want to keep my database as normalized as possible rather then result in storing a thumb field in the products table too.

  • 写回答

1条回答 默认 最新

  • duande1985 2016-02-01 20:05
    关注

    Untested, and there may be a better (performance) approach to the subselect... on PI I'm doing.

    Effectively what this is tying to do is return all products, the related feature products, and shops. Then return only those images which have a matching product; but only if the image is the product image with the lowest position. This way the product, feature_product and shops don't get excluded if an image is missing (thus the nature of a LEFT [outer] join)

    SELECT *
    FROM PRODUCTS P
    INNER JOIN Featured_Products FP
     on P.ProductID = FP.ProductID
    INNER JOIN SHOP S
     P.ShoPID = S.ShopID
    LEFT JOIN (Select thmbsrc, productID, position, min(Position) as MinPosition
               FROM Product_Image PI
               group by thmbsrc, productID, Position
               having MinPosition = position) PI
     on PI.ProductID = P.ProductID
    

    Performance may be improved if we first get a list of all the productIDs with the lowest position and then join it back to the product_image to get imgsrc. Something like (Select min(position) minPos, productID from product_Image) may allow the SQL engine to look at each record once, and not for every value in a having clause (if position is indexed then this would even be faster).

    So...

    SELECT *
    FROM PRODUCTS P
    INNER JOIN Featured_Products FP
     on P.ProductID = FP.ProductID
    INNER JOIN SHOP S
     P.ShoPID = S.ShopID
    LEFT JOIN (Select imgsrc FROM Product_Image PI
               INNER JOIN (Select min(position) minPos, productID 
                           from product_Image
                           GROUP BY ProductID) PI2
                on PI.ProductID = PI2.ProductID
               and PI.position = PI2.PminPos)
     on PI.ProductID = P.ProductID
    

    May perform better depending on table statistics and available indexes.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗