Custom inventory query statement - Need help please

[AndyM]

If I understand the problem, try adding "GROUP BY products_id" between the WHERE and ORDER clauses. I don't have any linked products in my database to test this with, so all bets are off here.

You should end up with something like this:

WHERE products_status =1
GROUP BY products_id
ORDER BY products_last_modified DESC

[0be1]

AndyM;

I tried this and here is what came back:

#1052 - Column: 'products_id' in group statement is ambiguous

And here is what the query now looks like:

SELECT CONCAT('http://', products_url) "Product Url", products_name "Product Name", products_description "Product Description", CONCAT( 'http://www.swansoninc.com/images/', products_image ) "Image URL", categories_name "Product Category", products_price "Product Price"
FROM zen_products_description
JOIN zen_products
USING ( products_id )
JOIN zen_products_to_categories
USING ( products_id )
JOIN zen_categories
USING ( categories_id )
JOIN zen_categories_description
USING ( categories_id )
WHERE products_status =1
GROUP BY products_id
ORDER BY products_last_modified DESC

Also since it was never mentioned I am on a server that is running MySQL 4.0.26. Please make sure any post replies refer to MySQL 4, not version 5.

thanks...

0be1

[AndyM]

None of this should be specific to 5. It's pretty generic SQL.

Try using GROUP BY zen_products.products_id instead. MySQL can't figure out which products_id you mean (even though they're JOINed and should be the same anyway).

[0be1]

AndyM;

That Worked!!! Thank you soooooooooo much. I do have another question. Do you ever sleep (LOL). Now if I can just figure out why my data is trashed on the output I will be good to go.

Thanks again...

0be1

[AndyM]

I'm glad the query worked.

I do sleep, just not as much as I'd like to.