select statement with multiple where/and is never finishing

[quasiman]

Hello!

-- phpMyAdmin SQL Dump
-- version 2.11.9.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 03, 2010 at 05:53 AM
-- Server version: 5.0.85
-- PHP Version: 5.2.5

CREATE TABLE IF NOT EXISTS `data_brandnames` (
  `ID` int(11) NOT NULL auto_increment,
  `BrandShort` varchar(100) NOT NULL,
  `BrandLong` varchar(100) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `data_inventory_status` (
  `ID` int(11) NOT NULL auto_increment,
  `SKU` varchar(40) NOT NULL,
  `Available` int(11) NOT NULL,
  `OpenPO` int(11) NOT NULL,
  `Exprecdate` datetime NOT NULL,
  `Status` varchar(4) NOT NULL,
  `Cost` decimal(10,2) NOT NULL,
  `MSRP` decimal(10,2) NOT NULL,
  `LowestCost` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `SKU` (`SKU`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `data_products` (
  `ID` int(11) NOT NULL auto_increment,
  `SKU` varchar(30) NOT NULL,
  `ShortDesc` text NOT NULL,
  `UPCCode` varchar(30) NOT NULL,
  `Weight` decimal(10,2) NOT NULL,
  `Family` varchar(100) NOT NULL,
  `ClassCode` varchar(30) NOT NULL,
  `MiscCode` varchar(30) NOT NULL,
  `AvailabilityCode` varchar(30) NOT NULL,
  `LongDesc` text NOT NULL,
  `DateAdded` datetime NOT NULL,
  `BrandCode` varchar(30) NOT NULL,
  `SectionCode` varchar(30) NOT NULL,
  `Hazardous` varchar(5) NOT NULL,
  `ORMD` varchar(5) NOT NULL,
  `Oversize` varchar(10) NOT NULL,
  `Restricted` varchar(5) NOT NULL,
  `New` varchar(5) NOT NULL,
  `MSRP` decimal(10,2) NOT NULL,
  `Dealer` decimal(10,2) NOT NULL,
  `MAAP` decimal(10,2) NOT NULL,
  `ItemCategory` varchar(30) NOT NULL,
  `CountryofOrigin` varchar(30) NOT NULL,
  `ManufactPartNumber` varchar(30) NOT NULL,
  `Company_Code` varchar(10) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `SKU` (`SKU`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `data_sectionnames` (
  `ID` int(11) NOT NULL auto_increment,
  `SectionShort` varchar(100) NOT NULL,
  `SectionLong` varchar(100) NOT NULL,
  KEY `ID` (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

I'm trying to run a select query that pulls info from 4 tables containing my raw inventory data, and comparing it to another table for my shop products.  The idea is to see which products don't exist in my shop yet.

data_products is the main table, and has identifiers for the other 3, of inventory status, brand name (abbreviations and full), and sections (basically categories)

 

I'm using "inv.Available != '0' " because I don't want to bother with products out of stock.

 

Anyway, here's the sql code I'm using.  I've tried this in a PHP script, and also just in phpMyAdmin

SELECT 
			pro.SKU, 
			pro.BrandCode, 
			pro.SectionCode,
			pro.Family,
			pro.ShortDesc,
			sect.SectionShort,
			sect.SectionLong,
			br.BrandLong,
			br.BrandShort,
			inv.SKU, 
			inv.Available,
			inv.Cost 
FROM 	data_products pro,
	data_inventory_status inv,
	data_brandnames br,
	data_sectionnames sect
WHERE pro.SKU NOT IN (SELECT `product_sku` FROM `shop_product`)
	AND inv.Available != '0' 
	AND pro.SKU = inv.SKU
	AND br.BrandShort = pro.BrandCode
	AND pro.SectionCode = sect.SectionShort
	LIMIT 10

 

 

Hope someone can help me out!

[fenway]

And why not a LEFT JOIN... iS NULL?

[quasiman]

good grief....of course.

Thanks, I've got it fixed