Hey SP,
I’m trying to join three tables. I want some items from table “item_inventory” to be displayed if they are appear in the right category and are also set as a “best seller”. The best seller is set in a table called item_field_data. If there is a record in item_field_data with the column item_field_id set to 85 and the column item_field_value set to 1 then it is a “best seller”. Also, there a huge schwack of categories it can appear in. Here is my query.
SELECT
DISTINCT i.item_inventory_id, i.item_inventory_title, i.item_type_id, i.item_inventory_description, i.item_inventory_price
FROM
item_inventory i
LEFT JOIN
item_field_data ifd ON ifd.item_inventory_id = ifd.item_inventory_id
LEFT JOIN
item_category_data icd ON i.item_inventory_id = icd.item_inventory_id
WHERE
i.item_type_id = "1" AND
i.item_inventory_public = "1" AND
ifd.item_field_id = "85" AND
ifd.item_field_value = "1" AND
( icd.item_category_id = "3094" OR icd.item_category_id = "10" OR icd.item_category_id = "2883" OR icd.item_category_id = "2884" OR icd.item_category_id = "2885" OR icd.item_category_id = "2886" OR icd.item_category_id = "2887" OR icd.item_category_id = "2888" OR icd.item_category_id = "2891" OR icd.item_category_id = "2892" OR icd.item_category_id = "2893" OR icd.item_category_id = "2894" OR icd.item_category_id = "2918" OR icd.item_category_id = "2988" OR icd.item_category_id = "2989" OR icd.item_category_id = "1411" OR icd.item_category_id = "2677" OR icd.item_category_id = "2680" OR icd.item_category_id = "2890" OR icd.item_category_id = "2949" OR icd.item_category_id = "2681" OR icd.item_category_id = "2683" OR icd.item_category_id = "2773" OR icd.item_category_id = "2774" OR icd.item_category_id = "2783" OR icd.item_category_id = "2784" OR icd.item_category_id = "2788" OR icd.item_category_id = "2789" OR icd.item_category_id = "2700" OR icd.item_category_id = "2698" OR icd.item_category_id = "2701" OR icd.item_category_id = "2766" OR icd.item_category_id = "2778" OR icd.item_category_id = "2785" OR icd.item_category_id = "2875" OR icd.item_category_id = "2899" OR icd.item_category_id = "2908" OR icd.item_category_id = "2753" OR icd.item_category_id = "2754" OR icd.item_category_id = "2180" OR icd.item_category_id = "2755" OR icd.item_category_id = "2780" OR icd.item_category_id = "2795" OR icd.item_category_id = "2807" OR icd.item_category_id = "2767" OR icd.item_category_id = "2961" OR icd.item_category_id = "2962" OR icd.item_category_id = "2963" OR icd.item_category_id = "3044" OR icd.item_category_id = "3045" OR icd.item_category_id = "3046" OR icd.item_category_id = "3102" OR icd.item_category_id = "3103" OR icd.item_category_id = "3047" OR icd.item_category_id = "2768" OR icd.item_category_id = "2870" OR icd.item_category_id = "2871" OR icd.item_category_id = "2897" OR icd.item_category_id = "2772" OR icd.item_category_id = "2776" OR icd.item_category_id = "2786" OR icd.item_category_id = "2790" OR icd.item_category_id = "2792" OR icd.item_category_id = "2793" OR icd.item_category_id = "2794" OR icd.item_category_id = "2791" OR icd.item_category_id = "2854" OR icd.item_category_id = "2855" OR icd.item_category_id = "2856" OR icd.item_category_id = "2857" OR icd.item_category_id = "2858" OR icd.item_category_id = "2898" OR icd.item_category_id = "2907" OR icd.item_category_id = "3023" OR icd.item_category_id = "3025" ) ORDER BY i.date_created desc LIMIT 15
The problem I am having is that the query seems to not care whether or not the item is set as a best seller or not. If I remove the extra table join for the category matching the query executes as expected. So I figure I’m just doing the wrong kind of join or I have the joins out of order? I’m not really sure, but I’ve tried a whole bunch of things and none of them gave me the expected result, so now I turn to trusty old sitepoint for an answer.
Thanks in advanced!