title: How to include another table in JOIN
body: <p>Below is my mysql query</p>
  
  <pre><code>SELECT 
      opensalesorder.so_number,
      items.VendorName,
      opensalesorder.item_number,
      items_custom_fields.FieldValue AS `Stock Item`,
      vendor_custom_fields.FieldValue AS `Paid Freight Allowance`,
      items.QuantityOnHand,
      items.ReorderPoint,
      items.MaxQty,
      SUM(opensalesorder.quantity_on_order),
      items.PurchaseCost,
      items.VendorName,
      items.VendorName,
      items.PurchaseCost,
      opensalesorder.status,
      items.ItemType
  FROM
      vendor,
      `opensalesorder` 
  inner join  items 
    on opensalesorder.item_number = items.ItemName
  JOIN items_custom_fields 
    ON items_custom_fields.ItemName = items.ItemName
  JOIN vendor_custom_fields 
    ON vendor_custom_fields.VName = vendor.VName
  WHERE opensalesorder.item_number = items.ItemName 
          and items_custom_fields.FieldName ='Stock Item' 
          and vendor_custom_fields.FieldName ='Paid Freight Allowance' 
          and opensalesorder.status NOT LIKE 'on po'  
          AND opensalesorder.so_number NOT IN ('2','3') 
          AND items.VendorName NOT IN ('Access') 
          AND opensalesorder.item_number NOT IN ('018-0001')  
          group by opensalesorder.item_number
  LIMIT 100
  </code></pre>
  
  <p>on executing this query I am getting error like</p>
  
  <pre><code>#1054 - Unknown column 'vendor.VName' in 'on clause' 
  </code></pre>
  
  <p>But I have included the vendor table in FROM clause.
  Is this right way to include a table in JOIN ?
  So whats wrong is with this query ?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>SHOW CREATE TABLE FOR opensalesorder</p>
  
  <pre><code>CREATE TABLE `opensalesorder` (
    `so_number` decimal(10,0) NOT NULL,
    `item_number` varchar(20) NOT NULL,
    `quantity_on_order` int(11) NOT NULL,
    `quantity_to_order` int(11) NOT NULL,
    `status` varchar(20) NOT NULL,
    `editsequence` text NOT NULL,
    `TxnLineID` text NOT NULL,
    `TxnID` text NOT NULL,
    `dateCreated` date NOT NULL,
    `shipDate` date NOT NULL,
    `customer` text NOT NULL,
    `itemclass` text NOT NULL,
    UNIQUE KEY `unique_mapping` (`so_number`,`item_number`),
    KEY `so_number` (`so_number`),
    KEY `item_number` (`item_number`),
    KEY `status` (`status`)
  )  
  </code></pre>
  
  <p>SHOW CREATE TABLE FOR items</p>
  
  <pre><code>CREATE TABLE `items` (
    `ItemName` varchar(30) NOT NULL,
    `VendorName` varchar(40) DEFAULT NULL,
    `QuantityOnHand` int(11) DEFAULT NULL,
    `QuantityOnSalesOrder` int(11) DEFAULT NULL,
    `ReorderPoint` int(11) DEFAULT NULL,
    `PurchaseCost` double DEFAULT NULL,
    `AverageCost` double DEFAULT NULL,
    `SalesPrice` double DEFAULT NULL,
    `PurchaseDesc` varchar(200) DEFAULT NULL,
    `SalesDesc` varchar(200) DEFAULT NULL,
    `ItemType` varchar(30) DEFAULT NULL,
    `FreeCode` int(11) DEFAULT NULL,
    `SubGroup` varchar(10) DEFAULT NULL,
    `DateNewItem` date DEFAULT NULL,
    `Notes` text,
    `MaxQty` int(11) DEFAULT NULL,
    `QuantityOnPO` int(11) DEFAULT NULL,
    PRIMARY KEY (`ItemName`),
    KEY `ItemName` (`ItemName`),
    KEY `VendorName` (`VendorName`)
  )
  </code></pre>
  
  <p>SHOW CREATE TABLE FOR vendor_custom_fields</p>
  
  <pre><code>CREATE TABLE `vendor_custom_fields` (
    `VName` text NOT NULL,
    `FieldName` text NOT NULL,
    `FieldValue` text NOT NULL,
    `FieldType` text NOT NULL,
    PRIMARY KEY (`VName`(120),`FieldName`(120)),
    FULLTEXT KEY `VName_index` (`VName`)
  ) 
  </code></pre>
  
  <p>SHOW CREATE TABLE FOR vendor</p>
  
  <pre><code> CREATE TABLE `vendor` (
    `VName` varchar(60) NOT NULL,
    `CompanyName` varchar(100) NOT NULL,
    `Address1` varchar(120) NOT NULL,
    `Address2` varchar(120) NOT NULL,
    `City` varchar(40) NOT NULL,
    `State` varchar(50) NOT NULL,
    `PostalCode` varchar(13) NOT NULL,
    `Phone` varchar(13) NOT NULL,
    `Fax` varchar(13) NOT NULL,
    `AlternatePhone` varchar(13) NOT NULL,
    `AlternateContact` varchar(30) NOT NULL,
    `Email` varchar(40) NOT NULL,
    `AccountNumber` varchar(30) NOT NULL,
    `Balance` double NOT NULL,
    `RepEmail` varchar(40) NOT NULL,
    `FreightAllowance` double DEFAULT NULL,
    `MinimumPOLimit` double DEFAULT NULL,
    `Notes` text NOT NULL,
    PRIMARY KEY (`VName`)
  ) 
  </code></pre>