MySQL

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

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2007

Posts: 4

Reputation: organicclothes is an unknown quantity at this point

Solved Threads: 0

organicclothes organicclothes is offline

Offline

Newbie Poster

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

May 29th, 2009

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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"
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

•

Join Date: Oct 2005

Posts: 521

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

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

May 29th, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

•

Originally Posted by 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:
Help with Code Tags

MySQL Syntax (Toggle Plain Text)
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"
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

Note to self... pocket cup

•

Join Date: Jan 2007

Posts: 4

Reputation: organicclothes is an unknown quantity at this point

Solved Threads: 0

organicclothes organicclothes is offline

Offline

Newbie Poster

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

May 29th, 2009

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

•

Join Date: Oct 2005

Posts: 521

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

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

May 29th, 2009

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.

•

Originally Posted by 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