...

View Full Version : Query in two tables



xenios
05-29-2011, 09:19 PM
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!

bazz
05-29-2011, 10:43 PM
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

Old Pedant
05-30-2011, 12:30 AM
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:


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.

xenios
05-30-2011, 07:26 AM
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.

bazz
05-30-2011, 10:02 AM
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.



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. :)




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.



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.




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



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



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


| 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

xenios
05-30-2011, 06:20 PM
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

bazz
05-30-2011, 06:56 PM
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

Old Pedant
05-30-2011, 07:36 PM
To answer the direct question:


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

xenios
05-31-2011, 06:30 PM
To answer the direct question:


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! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum