The other day I had a request from a client that wanted a list of all products in their store, that didn’t have any related product links. The list was required so that they could then login to the Magento admin and add related items to each of the products, ultimately to minimise any potential lost sales opportunities. Without the list, they would have been faced with the rather laborious task of manually checking each one.

The information was required to be in CSV format, detailing each product’s SKU, name and url. The solution didn’t need to be performant, nor did it need to be supplied again any time soon, therefore running a raw SQL query and exporting the results, was the ideal solution to give them the information that they required.

The query below will list all products that DO NOT have any related products and is reliant upon using the Magento flat catalog table feature. You can enable flat catalog by navigating to System > Configuration > Catalog > Frontend > Use Flat Catalog Product and setting this option to yes. If the store is not using flat catalog for any reason, then I strongly suggest that you do this locally.

The tables in question that we are querying are:

  • “catalog_product_flat_{store_id}”
  • “catalog_product_link”

This query is actually very easy to tweak so that we can use it to get all products with or without any other type of linked product.

Changing the query for a different type of linked product or reversing the logic

Change the link_type_id:

  • 1 = relation
  • 2 = bundle
  • 3 = super
  • 4 = up_sell
  • 5 = cross_sell

If we wanted reverse the logic of the query to get all products that DO have related products or another type of linked product, then change the sub query to “IN” rather than “NOT IN



Notify of