doudaochu1699 2016-02-01 11: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 12: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.

    展开全部

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

报告相同问题?

悬赏问题

  • ¥15 unity安卓打包出现问题
  • ¥15 爱快路由器端口更改错误导致无法访问
  • ¥20 安装catkin时遇到了如下问题请问该如何解决呢
  • ¥15 VAE模型如何输出结果
  • ¥15 编译python程序为pyd文件报错:{"source code string cannot contain null bytes"
  • ¥20 关于#r语言#的问题:广义加行模型拟合曲线后如何求拐点
  • ¥15 fluent设置了自动保存后,会有几个时间点不保存
  • ¥20 激光照射到四象线探测器,通过液晶屏显示X、Y值
  • ¥50 数据库开发问题求解答
  • ¥15 安装anaconda时报错
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部