SQL Query not quite pulling the data I expected...

[organicclothes]

Hi,
I've managed to piece together an SQL query (not literate in the language), but it isn't giving quite the data I expected. Here is the code:

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description
INNER JOIN (
products, salemaker_sales
) ON ( products_description.products_id = products.products_id
AND products.master_categories_id = salemaker_sales.sale_categories_selected ) 
WHERE products.products_status =1
AND salemaker_sales.sale_status = "1"

The trouble I am seeing is this:
salemaker_sales.sale_categories_selected contains multiple variables separated by a comma

This query is only returning the first variable. What am I missing?

Any help much appreciated,
Audra

[pty]

Not really sure what you're asking. What column type is sales_categories?

Also your SQL is confusing; try aliasing the table names and aligning it:

SELECT		pd.products_name, 
	        pd.products_description, 
	        pd.products_id, 
	        p.products_image, 
                p.products_price_sorter
FROM 		products_description as pd
INNER JOIN 	products AS p ON pd.products_id = p.products_id
INNER JOIN 	salemaker_sales AS ss ON p.master_categories_id = ss.sale_categories_selected ) 
WHERE 		p.products_status = 1
AND 		ss.sale_status = 1

Hi,
I've managed to piece together an SQL query (not literate in the language), but it isn't giving quite the data I expected. Here is the code:

SELECT products_description.products_name, products_description.products_description, products_description.products_id, products.products_image, products.products_price_sorter
FROM products_description
INNER JOIN (
products, salemaker_sales
) ON ( products_description.products_id = products.products_id
AND products.master_categories_id = salemaker_sales.sale_categories_selected ) 
WHERE products.products_status =1
AND salemaker_sales.sale_status = "1"

The trouble I am seeing is this:
salemaker_sales.sale_categories_selected contains multiple variables separated by a comma

This query is only returning the first variable. What am I missing?

Any help much appreciated,
Audra

[organicclothes]

Thank you pty, what you have done is certainly easier to follow. Running that query leads to the same result I experienced.

The sales_categories column is a text type column.

In this instance, the result will only lead to one entry w/ the data "48,76,129"

Each of those numbers corresponds to a p.master_categories_id

The results yield the products in p.master_categories_id 48 and then stops there.
I'm guessing the problem lies in the fact that this is a text field?

I apologize if my description is confusing. I do not have the SQL lingo, so I speak in laymens' terms.

Peace,
Audra

[pty]

I misunderstood your first post.

If the column is varchar and you have a comma separated list of IDs there isn't a clean way of joining.

You should review your table design and possibly introduce a link table (many to many relationship). I don't know exactly how your data is meant to be so I can't suggest exactly what to do, but rather than having many items in a list inside one column you should organise your data so each list item is a row.

Thank you pty, what you have done is
certainly easier to follow. Running that query leads to the same result I experienced.

The sales_categories column is a text type column.

In this instance, the result will only lead to one entry w/ the data "48,76,129"

Each of those numbers corresponds to a p.master_categories_id

The results yield the products in p.master_categories_id 48 and then stops there.
I'm guessing the problem lies in the fact that this is a text field?

I apologize if my description is confusing. I do not have the SQL lingo, so I speak in laymens' terms.

Peace,
Audra