SitePoint Sponsor

User Tag List

Results 1 to 1 of 1

Threaded View

  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [Solved] Why isn't my triple join working?

    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.

    Code:
    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!
    Last edited by wh33t; Nov 29, 2012 at 15:33. Reason: Nvm, typo solved it.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •