SQL ???

[Guest]

All,

 

I have this query

SELECT p.products_id, pd.products_name, p.manufacturers_id, p2c.categories_id, pd.language_id, p.products_status
FROM (((products AS p LEFT JOIN manufacturers AS m ON p.manufacturers_id = m.manufacturers_id) LEFT JOIN products_description AS pd ON p.products_id = pd.products_id) LEFT JOIN products_to_categories AS p2c ON p.products_id = p2c.products_id) LEFT JOIN specials AS s ON p.products_id = s.products_id
WHERE (((pd.products_name) Is Not Null) AND ((p2c.categories_id)=26) AND ((pd.language_id)=1) AND ((p.products_status)=1));

 

It works both in MS-Access via ODBC to the MySQL DB and via the ISPs ISQL interface.

 

However when I put it into the OSC code the TEP engine always blows.

 

I've re-written the query over 26 different ways, that work in ODBC and ISQL, but always errors in TEP.

 

What's up with this, What do I need to do to quit pulling my hair (not that I have any left) :) :lol: :P

 

Help please!!!!

 

OMR

[Guest]

What are you trying to accomplish with this query??

 

Try this:

SELECT p.products_id, pd.products_name, p.manufacturers_id, p2c.categories_id, pd.language_id, p.products_status 
FROM products p, products_description pd, products_to_categories p2c 
LEFT JOIN manufacturers m 
	ON (p.manufacturers_id = m.manufacturers_id) 
LEFT JOIN specials s 
	ON (p.products_id = s.products_id) 
WHERE p.products_status = '1' 
AND p.products_id = pd.products_id 
AND pd.language_id= '1' 
AND pd.products_name is not null 
AND p2c.products_id = p.products_id 
AND p2c.categories_id = '26'

[Guest]

All,

 

I think I got around this one, but not sure. Done too many to remember, that's bad.

 

Anyway I'm finding on MySQL that INNER JOINs and OUTER JOINs have problems. It's better to write the code I gave in the example like:

SELECT p.products_id, pd.products_name, p.manufacturers_id, p2c.categories_id, pd.language_id, p.products_status
FROM products p, products_description pd, products_to_categories p2c, manufacturers m, specials s
WHERE p.products_status = '1'
AND p.products_id = pd.products_id
AND p.manufacturers_id = m.manufacturers_id
AND p.products_id = s.products_id
AND pd.language_id= '1'
AND pd.products_name is not null
AND p2c.products_id = p.products_id
AND p2c.categories_id = '26'

instead of fighting the JOINs.

 

OMR