SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help on Querying Specific Products from a table

    Hello,

    Having a category table like this:

    Code:
    id_cat
    name_cat
    description_cat
    parent_id_cat
    and a product table like this:
    Code:
    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

  2. #2
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    ...what if we want to retrieve all cars where the parent_id_cat is equal to 2 ?
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello r937,

    Let's see if I get this:


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

    Code:
    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:


    Code:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you've never encountered INNER JOIN syntax before?

    please do a search for "SQL tutorial"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oikram View Post
    One last question, since we know the levels, if we have 3 depth levels, we should inner join twice?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.

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

    Again, thanks for you availability,
    Márcio

  9. #9
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

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

    Code:
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

    Quote Originally Posted by r937 View Post
    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.

    Quote Originally Posted by r937 View Post
    Code:
    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

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •