Get count of unmapped records when using a mapping table



  • I have 3 tables as such

    Product

    Product ID Product Details
    1 ...
    2 ...
    3 ...

    Vendor

    Vendor ID Vendor Details
    1 ...
    2 ...
    3 ...

    Product Vendors

    Product ID Vendor ID
    1 1
    2 1
    1 2
    2 2
    3 2

    How would I go about finding the number of products that are not mapped to a specific vendor.

    I tried:

    SELECT
        COUNT(pr.id) AS product_count
    FROM
        products pr
    LEFT JOIN vendor_product_map vp ON
        pr.id = vp.product
    LEFT JOIN vendors vv ON
        vp.vendor = vv.id
    WHERE
        vv.id = 3 AND vp.vendor IS NULL
    

    but that doesn't seem right. Any help is appreciated

    Edit: For the above data, the expected results would be

    Not Mapped Products

    Vendor ID Product Count
    1 1
    2 0
    3 3

    In short not mapped product count = total products - count of mapped products for a specific vendor, I'll be specifying the vendor ID so the result set will have only 1 row. Hope that clarifies.



  • I got it to work using not exists and a subquery.

    SELECT
        COUNT(pr.id) AS product_count
    FROM
        products pr
    WHERE NOT EXISTS(
        SELECT
            *
        FROM
            vendor_product_map vp
        WHERE
            vp.product = pr.id AND vp.vendor = 3
    )
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2