Help on Querying Specific Products from a table

[oikram]

Hello,

Having a category table like this:

id_cat
name_cat
description_cat
parent_id_cat

and a product table like this:

id_prod  	
id_cat 
nome_prod

Let's say we have several products that belong to a specific category, and that category, belongs to a specific parent category, and we want to list all products that belong to that parent category.

Categories:
1 - House
2 - Car
2.1 europe (parent_id_cat 2)
2.2 us (parent_id_cat 2)

Products:
Opel (id_cat = 2.1)
Ford (id_cat = 2.2)

We can select all cars where id_cat = 2.1, but what if we want to retrieve all cars where the parent_id_cat is equal to 2 ?


Please advice,

Kind Regards,
Márcio

[oikram]

As an add on:

we are talking about a 1-n relationship.
And the deep level will be fixed.


Recursion can be used on the server side scripting side correct? I was just trying to see if, it's possible to use only mysql query and if so, what whould be the inconvenient? Database intensive? (It will be a low traffic website, no more then 1000 visits month I believe).

Thanks again,
Márcio

[r937]

...what if we want to retrieve all cars where the parent_id_cat is equal to 2 ?

SELECT product.id_prod  	
     , product.id_cat 
     , product.nome_prod
  FROM category 
INNER
  JOIN product
    ON product.id_cat = category.id_cat
 WHERE category.parent_id_cat = 2

[oikram]

Hello r937,

Let's see if I get this:


It seems a strange query when we see it only like this :

SELECT product.id_prod  	
     , product.id_cat 
     , product.nome_prod
  FROM category

because we are telling, select product fields, from a category table (that doesn't have those fields), however, we can do that, because we are
"inner joining" like this:

INNER
  JOIN product
    ON product.id_cat = category.id_cat
 WHERE category.parent_id_cat = 2

However, I'm having a hard time trying to figuring out, what does JOIN ON really means here, can I have a few words on that?



Kind regards,
Márcio

[r937]

you've never encountered INNER JOIN syntax before?

please do a search for "SQL tutorial"

[oikram]

I have. I have seen the ON here and there.

And I have seen it here as well:
http://dev.mysql.com/doc/refman/5.1/en/join.html

"ON conditional_expr } "

But my question was more semantic then anything else I presume, "how should we read this ON ?"

I've found the buzzword clause. And searching ON CLAUSE I will end up find something.


One last question, since we know the levels, if we have 3 depth levels, we should inner join twice?


Thanks again,
Márcio

[r937]

One last question, since we know the levels, if we have 3 depth levels, we should inner join twice?

yes

[oikram]

Thanks a lot.

I will give it a try and post back if any issues arrive.

Again, thanks for you availability,
Márcio

[oikram]

Hello again,

Can I please ask your help once more... :s
I'm having the following right now:

SELECT p.id_prod, p.id_cat, p.nome_prod
FROM produto p
INNER JOIN categoria c ON c.id_cat = p.id_cat
INNER JOIN categoria c2 ON c.parent_id_cat = c2.id_cat
WHERE c2.parent_id_cat =2

Categories:
1 - House
2 - Car
2.1 - Truck
2.1.1 - europe (parent_id_cat 2.1)
2.1.2 - us (parent_id_cat 2.1)
2.2 - Sportive


Products:
Opel Truck (id_cat = 2.1.1)
Ford Truck (id_cat = 2.1.2)
Skoda Sportive (id_cat 2.2)


The query above, allow us to deep 2 levels on the tree data, and retrieve
Opel Truck and Ford Truck.

My question is: Is there a possibility to also allow, the retrieval of Skoda Sportive that is one level up ?


Thanks in advance,
Márcio

[r937]

i must be honest and tell you that your c and c2 tables make me confused

the answer to your question is yes, you can get products at two different levels, by using a UNION query

SELECT p.id_prod
     , p.id_cat
     , p.nome_prod
  FROM categoria one 
INNER
  JOIN categoria two
    ON two.id_cat = one.parent_id_cat
INNER 
  JOIN produto p
    ON p.id_cat = two.id_cat
 WHERE one.parent_id_cat =2
UNION ALL
SELECT p.id_prod
     , p.id_cat
     , p.nome_prod
  FROM categoria one 
INNER 
  JOIN produto p
    ON p.id_cat = one.id_cat
 WHERE one.parent_id_cat =2

that might not run for your situation but i trust you will get the idea from it

[oikram]

i must be honest and tell you that your c and c2 tables make me confused

Why is so? My main objective was only to distinguish them. But I realise I don't truly understand what's going on (inside) this process.

the answer to your question is yes, you can get products at two different levels, by using a UNION query

I will dig in on the UNION keyword.

SELECT p.id_prod
     , p.id_cat
     , p.nome_prod
  FROM categoria one 
INNER
  JOIN categoria two
    ON two.id_cat = one.parent_id_cat
INNER 
  JOIN produto p
    ON p.id_cat = two.id_cat
 WHERE one.parent_id_cat =2
UNION ALL
SELECT p.id_prod
     , p.id_cat
     , p.nome_prod
  FROM categoria one 
INNER 
  JOIN produto p
    ON p.id_cat = one.id_cat
 WHERE one.parent_id_cat =2

that might not run for your situation but i trust you will get the idea from it

At the first glass it really seems complex on my newbie eyes. I will use it, and try to see what I get with the UNION investigation search I will made.

Thanks a lot, really,
Márcio

[oikram]

Dear r937

Thanks a lot for your help on this. It works like a charm.
Instead of UNION ALL I've used UNION because I don't want to retrieve duplicate values on this case, mutatis mutantis, here is the final working code thanks to you:

SELECT p.id_prod, p.id_cat, p.nome_prod
FROM produto p
INNER JOIN categoria c ON c.id_cat = p.id_cat
INNER JOIN categoria c2 ON c.parent_id_cat = c2.id_cat
WHERE c2.parent_id_cat = 1
UNION
SELECT p.id_prod, p.id_cat, p.nome_prod
FROM produto p
INNER JOIN categoria c ON c.id_cat = p.id_cat
WHERE c.parent_id_cat = 1

Please see if this is precise:

1) We are putting together the result of two SELECT statements, by using UNION. If one select returns 0, and the other returns some data, we will get that some data. If both return data, we will get the distinct data values of both queries united.

2)
2.1 The first select statement, selects some fields from a given table.
2.2) Then it joins another table (categoria), on a temporary table named c,
2.2.2) on the condition that category id is equal to product category id.
2.3) It then self joins (the same categoria table), and put that result on a temporary table, named c2, on the condition that the first joined categoria row parent_id_cat, is equal to this new table (c2) row id_cat.

2.4)All this will happen only when the parent_id_cat of the newest created table is equal to 1.

3) it will... process the second (less complex btw) similar query.




Regards,
Márcio