Extract multiple columns from multiple tables

[Irvin]

For hours and hours I've been trying to work out how to write the sql query to extract some data from some tables, but with no luck. Please help.

 

Basically, we have these tables with the data fields as below:

 

Products

• products_id
  
• products_model
  
• products_price
  
• products_weight
  

products_to_categories

• products_id
  
• categories_id
  

products_description

• products_id
  
• products_name
  
• products_description
  

categories

• categories_id
  
• parent_id
  

categories_description

• categories_id
  
• categories_name
  

The fields that are needed to be output are as follow:

• categories_name (this categories name must come from parent_id)
  
• products_name
  
• products_model
  
• products_price
  
• products_weight
  
• products_description
  

The sql query I've done so far:

select cd.categories_name, pd.products_name, pd.products_description, p.products_model, p.products_price, p.products_weight, m.manufacturers_name, pd.products_head_title_tag, pd.products_head_desc_tag, pd.products_head_keywords_tag from categories c, products_to_categories p2c, categories_description cd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id where p.products_id = p2c.products_id and c.parent_id = p2c.categories_id;

But it doesn't come out right. By the way, this is an oscommerce stuff I'm working on. I'd just want to get all the product details from the database and my oscommerce shop has Main Category > Sub Category > Products.

[Velveeta]

But it doesn't come out right. By the way, this is an oscommerce stuff I'm working on. I'd just want to get all the product details from the database and my oscommerce shop has Main Category > Sub Category > Products.

Define "it doesn't come out right"... Is it returning no results at all, or too many results, or results that don't match up at all, or what? One problem I can see right off the bat is that you've got no language_id match in there for the products_description matches, so it would return all language entries, meaning in a default store, 3x the results you need, because there would be german and spanish as well as english... Give us a little more info and we should be able to help you fine tune that query...

 

Richard.

[Irvin]

Define "it doesn't come out right"... Is it returning no results at all, or too many results, or results that don't match up at all, or what? One problem I can see right off the bat is that you've got no language_id match in there for the products_description matches, so it would return all language entries, meaning in a default store, 3x the results you need, because there would be german and spanish as well as english... Give us a little more info and we should be able to help you fine tune that query...

 

Richard.

Hi Richard thank you very much for your reply.

 

The result is alright, except the categories_name. It turns out that the categories name duplicates itself.

 

I'd like the output to be:

Main Category Name, Sub Category Name, Products Name, Products Model, and so on.

I've tried GROUP BY, and DISTINCT but still no luck, please excuse my 'newbie' sql programming skill :blush:

 

The language_id shouldn't be a problem, since I only use the English language for my Osc, and got rid of all the other languages.

 

Could you please help?

[Velveeta]

Hi Richard thank you very much for your reply.

 

The result is alright, except the categories_name. It turns out that the categories name duplicates itself.

 

I'd like the output to be:

Main Category Name, Sub Category Name, Products Name, Products Model, and so on.

I've tried GROUP BY, and DISTINCT but still no luck, please excuse my 'newbie' sql programming skill :blush:

 

The language_id shouldn't be a problem, since I only use the English language for my Osc, and got rid of all the other languages.

 

Could you please help?

Well, if you're getting duplicate entries, it sounds like the tables aren't all being compared properly, try something like this:

 

select cd.categories_name, pd.products_name, pd.products_description, p.products_model, p.products_price, p.products_weight, m.manufacturers_name, pd.products_head_title_tag, pd.products_head_desc_tag, pd.products_head_keywords_tag from categories c, products_to_categories p2c, categories_description cd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_description pd on p.products_id = pd.products_id where p.products_id = p2c.products_id and c.parent_id = p2c.categories_id and c.categories_id = cd.categories_id;

I noticed you didn't have a comparison for your categories_description table in there, so that got tacked onto the end of the query, "and c.categories_id = cd.categories_id"... Also, you realize that as this is written, it's pulling product data from 1 level down, so if you have categories A -> B -> C (those are listed as subcategories), and you're viewing category B, it's going to be pulling data for category C, since it's comparing c.parent_id (rather than c.categories_id) to the p2c.categories_id... You're also going to want to feed it a seed category id or else that match is going to pull for all categories, meaning you'd want to add something like "and p2c.categories_id = '" . (int)$current_category_id . "'"...

 

Richard.

[Irvin]

I've tried the following query, but it only extracts the product's parent_id and so forth, but what I need is to get the category name of that parent_id instead of the sub category name.

 

select p.products_id, c.parent_id, c.categories_id from categories c, products_to_categories p2c, products p where c.categories_id = p2c.categories_id

and p2c.products_id = p.products_id;

 

Somebody has any idea how could I get the 'parent category name' of each of all the products?

[Velveeta]

I've tried the following query, but it only extracts the product's parent_id and so forth, but what I need is to get the category name of that parent_id instead of the sub category name.

Somebody has any idea how could I get the 'parent category name' of each of all the products?

If you want the parent category's name, you would do something like this:

 

select p.products_id, c.parent_id as categories_id, cd.categories_name from categories c, categories_description cd, products_to_categories p2c, products p where c.parent_id = p2c.categories_id and p2c.products_id = p.products_id and c.parent_id = cd.categories_id;

 

That should fetch the products id from whatever category, but the categories_id and categories_name values will be those of the parent category of the category that those products belong to...

 

Richard.

[Irvin]

Richard, you're...brilliant!!!

 

Thank you a million!

 

By the way, what should be added to the query if I want to output the Sub Category as well?

 

Would it be by adding 'categories_name' to the query as below?

 

select p.products_id, c.parent_id as categories_id, categories_name, cd.categories_name from categories c, categories_description cd, products_to_categories p2c, products p where c.parent_id = p2c.categories_id and p2c.products_id = p.products_id and c.parent_id = cd.categories_id;

[Velveeta]

Richard, you're...brilliant!!!

 

Thank you a million!

 

By the way, what should be added to the query if I want to output the Sub Category as well?

 

Would it be by adding 'categories_name' to the query as below?

 

select p.products_id, c.parent_id as categories_id, categories_name, cd.categories_name from categories c, categories_description cd, products_to_categories p2c, products p where c.parent_id = p2c.categories_id and p2c.products_id = p.products_id and c.parent_id = cd.categories_id;

If you wanted to grab subcategory info also, you'd have to search on the table more than once, like so:

 

select p.products_id, c.parent_id as parent_categories_id, c.categories_id, cd.categories_name as parent_categories_name, cd2.categories_name from categories c, categories_description cd, categories_description cd2, products p, products_to_categories p2c where p.products_id = p2c.products_id and c.parent_id = p2c.categories_id and c.parent_id = cd.categories_id and c.categories_id = cd2.categories_id;

 

That should grab the product id listing, the parent category id as parent_categories_id, the subcategory id as categories_id, the parent category name as parent_categories_name, and the subcategory name as categories_name...

 

Richard.

[Irvin]

If you wanted to grab subcategory info also, you'd have to search on the table more than once, like so:

 

select p.products_id, c.parent_id as parent_categories_id, c.categories_id, cd.categories_name as parent_categories_name, cd2.categories_name from categories c, categories_description cd, categories_description cd2, products p, products_to_categories p2c where p.products_id = p2c.products_id and c.parent_id = p2c.categories_id and c.parent_id = cd.categories_id and c.categories_id = cd2.categories_id;

 

That should grab the product id listing, the parent category id as parent_categories_id, the subcategory id as categories_id, the parent category name as parent_categories_name, and the subcategory name as categories_name...

 

Richard.

 

Hi Richard, thanks again for your reply.

Unfortunately, this query returns zero result, maybe there's something wrong with the sql query?

[Irvin]

perhaps, anyone else able to help? :s

[Guest]

If you wanted to grab subcategory info also, you'd have to search on the table more than once, like so:

 

select p.products_id, c.parent_id as parent_categories_id, c.categories_id, cd.categories_name as parent_categories_name, cd2.categories_name from categories c, categories_description cd, categories_description cd2, products p, products_to_categories p2c where p.products_id = p2c.products_id and c.parent_id = p2c.categories_id and c.parent_id = cd.categories_id and c.categories_id = cd2.categories_id;

 

That should grab the product id listing, the parent category id as parent_categories_id, the subcategory id as categories_id, the parent category name as parent_categories_name, and the subcategory name as categories_name...

 

Richard.

 

You can use the same alias a million times it simply has no effect. In addition you try to compare the categories_id with what? you expect a category's id to match its parent id? Aint gona work. If you want to extract the parent's info see how the tep_get_parent_categories works.

[Velveeta]

You can use the same alias a million times it simply has no effect. In addition you try to compare the categories_id with what? you expect a category's id to match its parent id? Aint gona work. If you want to extract the parent's info see how the tep_get_parent_categories works.

You're not comparing the categories_id to its parent_id within the same table, otherwise you're right, it would never match... You're comparing the category's parent_id to the categories_id in the categories_description table, to pull the name info for the parent category... As for using the same alias a million times, if you try to use a single table reference to match both the categories_id and the parent_id to, you'll run into the same problem you stated, you would never make a match, because you'll never find a categories_description entry that matches for both the categories_id and the parent_id... That's why you declare a 2nd table alias, to match the categories_id to one and the parent_id to the other...

 

Richard.