Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Query in two tables

    Hi,

    I have two tables

    Table categories
    +---+---------+------------+-----+
    | id | category | subcategory|name |
    +---+---------+------------+-----+

    Table rate
    +---+---- -+-----+
    | id | name| rate |
    +---+----- +-----+


    First table (categories) is used to create categories tree structure.
    Second table is used for categories ratings

    This is the query I use for the categories tree structure.

    $sql1 = "SELECT category, subcategory, name FROM categories ORDER BY category, subcategory";

    and I also have this query that returns the AVG rating of name from table rate

    $sql2 = "SELECT AVG(rate) FROM rate WHERE name = somename";

    How can I have the results from $sql1 and the AVG (rate) from table rate for each 'name' in a single query?

    Thank you!

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    can you post some sample data please. It would help to see how the id of the two tables might relate to each other and also the relationship between the two name columns.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Looks to me like, unfortunately, the ID's in the two tables are *NOT* related. I think he is using name to relate between the tables.

    xenios: *IF* that is true, then:
    Code:
    SELECT C.category, C.subcategory, C.name, AVG(R.rate) AS averageRate
    FROM categories AS C, rate as R
    WHERE C.name = R.name
    GROUP BY C.category, C.subcategory, C.name
    ORDER BY C.category, C.subcategory
    This is not the best design. You should be linking the two tables by some kind of id. But if name is UNIQUE in the categories table, it's okay.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    table rate already exists and is used from a rating script. I' am now writing a script that will use table categories and also pull out data from the existing rate table. The only relation between the two tables is, as Old Pedant mentioned, the name field. I know that if I was going to create the rate and categories tables from scratch, it could be done in a better way, but unfortunately table rate already exists.

    Old Pedant, your query works but it returns results only if C.name = R.name. If C.name is not present in table rate you get no results.
    Is it possible to have the query return all results from table categories and if C.name is found in table rate then it should also return AVG(R.rate), otherwise AVG(R.rate) should be null for C.name not found in table rate?

    I forgot to mention, yes, name is UNIQUE in the categories table.

    Thank you both for your time and answers.
    Last edited by xenios; 05-30-2011 at 07:29 AM.

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by Old_Pedant
    Looks to me like, unfortunately, the ID's in the two tables are *NOT* related.
    I reckoned so but, wanted to be sure before we took time to write a solution which would or could be wrong.


    Quote Originally Posted by xenios View Post
    table rate already exists and is used from a rating script. I know that if I was going to create the rate and categories tables from scratch, it could be done in a better way, but unfortunately table rate already exists.
    It could still be changed without busting anything but, I don't think we need to for this issue.


    Quote Originally Posted by xenios View Post
    I forgot to mention, yes, name is UNIQUE in the categories table.
    How can you be certain the name value will always be unique? I would alter the categories table as shown below.

    Code:
    create table categories
    ( id int not null auto_increment primary key
    , category varchar(99) not null
    , sub_category varchar(99) NULL
    , name_id int not null
    , constraint categories_rates_fk
        foreign key (name_id)
          references rates(id)
    Now, you may already have this part of your db done but, in case not; category->sub_category to the name is not likely to be a one-to-one relationship. So I suggest you have a separate table for 'categories_and_sub_categories'. It should store one of each pair of cats and sub-cats. Then in the table above, renamed to categories_names, the categories and sub_categories columns could be foreign keyed to the categories_sub_categories table. here's my suggestion for your new tables.

    Note: rate doesn't change though you can change its columns in the future for whatever reason without busting anything.

    here is my suggestion. beneath it, I shall write out the same statements but with explanations included.

    Code:
    create table rates
    ( id int not null auto_increment
    , name varchar(99) not null
    , rate decimal (5,2)
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
    
    create table categeories_and_sub_categories
    ( category varchar(99) not null
    , sub_category varchar(99) NULL
    , primary key (categories, sub_categories)
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
    
    
    create table categories_names (re-named from your current table, categories)
    ( id int not null auto_increment primary key
    , category varchar(99) not null
    , sub_category varchar(99) NULL
    , name_id int not null
    , constraint categories_rates_fk
        foreign key (name_id)
          references rates(id)
    , constraint categoriesNames_categoriesAndSubCategories_fk
        foreign key(category,sub_category)
          references categories_and_sub_categories(category,sub_category)
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;


    and here, with explanations
    Code:
    create table rates
    ( id int not null auto_increment # assigns a unique id so each record is unique
    , name varchar(99) not null # change the 99 to whatever is enough to allow the longest possible name
    , rate decimal (5,2) # anything up to 999.99  up to 100 000.20 to two decimal places would be (8,2)
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
    
    create table categeories_and_sub_categories
    ( category varchar(99) not null # one part of a composite key
    , sub_category varchar(99) NULL # the other part of the composite key
    , primary key (categories, sub_categories) # makes the key composite
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
    
    
    create table categories_names (re-named from your current table, categories)
    ( id int not null auto_increment primary key
    , category varchar(99) not null
    , sub_category varchar(99) NULL
    , name_id int not null
    # the relates the name_id to the 'id of its parent table (rates)
    , constraint categories_rates_fk
        foreign key (name_id)
          references rates(id) 
            on delete cascade 
            on update cascade
    # the relates the composite key to the same values in its parent table (categories_and_sub_categories) 
    , constraint categoriesNames_categoriesAndSubCategories_fk
        foreign key(category,sub_category)
          references categories_and_sub_categories(category,sub_category)
            on delete cascade
            on update cascade
    ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
    Now, you can query for your resultset but, also, if you change a category or want to remove it, you only need to delete it/alter it in the parent table - categories_and_sub_categories. the foreign key constraint will ensure deletion/change will be enforced by MySQL in the children table(s) throughout your db.

    search for such things as
    foreign key constraints
    database normalisation
    referential integrity
    adjacency list model


    The adjacency list model is quite good for categoreis and sub categories.
    In place of categories_and_sub_categories you could do that more easily inthis next table

    Code:
    create table categories
    (id int not null autro_increment primary key
    , category varchar(99) not null
    , parent_id int NULL
    , constraint parent_id_fk
        foreign key parent_id
          references categories(id) on delete cascade
    )
    data
    Code:
    | 1 | FORD | NULL |
    | 2 | Mercedes | NULL|
    | 3 | SLS |  2 |
    | 4 | Mustang | 1 |
    see how the parent id of Mustang (the sub_category), is that of the manufacturer (category), by relating the id/parent_id


    hth

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you bazz for the extensive post.
    I do not wish to alter the structure of existing tables, that's why I was looking for something more simple. The solution you propose is quite complicated for me.

    Thank you for your kind help.
    Regards

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    ok but; I recommend you take time to work through relatively simple part because it may bite you very hard later, if you don't.

    For example, normalisation is important. If you decide later to normalise - when you feel you are ready - you may have a lot more work to do because SCRIPTS and DATABASE will all have to be changed. Better to take a day now than two weeks, later.

    And I have given you the create table statements to make it easier.

    It's your call though.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    To answer the direct question:
    Code:
    SELECT C.category, C.subcategory, C.name, AVG(R.rate) AS averageRate
    FROM categories AS C LEFT JOIN rate as R
    ON C.name = R.name
    GROUP BY C.category, C.subcategory, C.name
    ORDER BY C.category, C.subcategory
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    xenios (05-31-2011)

  • #9
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    To answer the direct question:
    Code:
    SELECT C.category, C.subcategory, C.name, AVG(R.rate) AS averageRate
    FROM categories AS C LEFT JOIN rate as R
    ON C.name = R.name
    GROUP BY C.category, C.subcategory, C.name
    ORDER BY C.category, C.subcategory
    It works as I wanted to work.
    Thank you very much!


  •  

    Posting Permissions

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