multiple table query with MIN()
multiple table query with MIN()
(OP)
HI,
New to the forums and I apologize if this isn't being posted in the right area...
I am trying to run a pretty complex query (complex for me!!) and I have gotten the basic results i need but I am stuck on getting over the last hurdle.
I presently have this query that does what i need it to do.
SELECT SUM(activity)
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20);
I need to add another part to this query but it has gotten a little complicated for me. There are three tables....dates, foods, activities and i need to find the sum of a result set which contains the minimum number between two values from two different tables as long as a certain statement is true.
Basically..
SELECT SUM(total)
FROM (SELECT MIN(value from table1 which is determined by a value in table2, value from table3) AS total
FROM table3
WHERE value from table3 is contained in a result set from table1);
The below query is something I came up with that is actually what i need but it doesn't work. I am doing something wrong...
SELECT SUM(activity_amount)
FROM (SELECT min((SELECT SUM(points) - 20 FROM foods WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id), activity) AS activity_amount
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20));
I believe the problem is with the first value in the MIN()...."SELECT SUM(points) - 20 FROM food WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id"
That statement yields more than one value but even tho i do need those values to compare against others in the MIN(), I only need them one at a time...not as a whole set
Thanks for any help you might be able to provide
New to the forums and I apologize if this isn't being posted in the right area...
I am trying to run a pretty complex query (complex for me!!) and I have gotten the basic results i need but I am stuck on getting over the last hurdle.
I presently have this query that does what i need it to do.
SELECT SUM(activity)
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20);
I need to add another part to this query but it has gotten a little complicated for me. There are three tables....dates, foods, activities and i need to find the sum of a result set which contains the minimum number between two values from two different tables as long as a certain statement is true.
Basically..
SELECT SUM(total)
FROM (SELECT MIN(value from table1 which is determined by a value in table2, value from table3) AS total
FROM table3
WHERE value from table3 is contained in a result set from table1);
The below query is something I came up with that is actually what i need but it doesn't work. I am doing something wrong...
SELECT SUM(activity_amount)
FROM (SELECT min((SELECT SUM(points) - 20 FROM foods WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id), activity) AS activity_amount
FROM activities
WHERE activity_id IN(SELECT name_id FROM foods GROUP BY name_id HAVING SUM(points) > 20));
I believe the problem is with the first value in the MIN()...."SELECT SUM(points) - 20 FROM food WHERE name_id IN(SELECT pk FROM dates WHERE weekly=1) GROUP BY name_id"
That statement yields more than one value but even tho i do need those values to compare against others in the MIN(), I only need them one at a time...not as a whole set
Thanks for any help you might be able to provide
Talk To Other Members
RE: multiple table query with MIN()
RE: multiple table query with MIN()
2) Your requirements are too vague like "i do need those values to compare against others"???
3) Describe the tables
4) Provide sample data for the source tables
5) Provide expected result based on sample data
Otherwise, consulting my Ouiji board I get something lke this:
CODE
SELECT min((SELECT activity_id name_id IN(SELECT AS > WHERE activity_amount 20))
WHERE SUM(activity_amount) BY 20 BY GROUP name_id SUM(points) IN (
SELECT FROM - GROUP dates weekly=1)
SELECT FROM SUM(points) name_id FROM activity) FROM FROM WHERE pk ?
----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
RE: multiple table query with MIN()
Sorry about not providing a sample table. You're right, it would make things a lot easier.
Anyway...
CODE
CREATE TABLE dates (pk INTEGER PRIMARY KEY, date INTEGER, weekly INTEGER)
pk date weekly
1 05062009 1
2 05072009 1
3 05082009 2
Table #2(foods)
CREATE TABLE foods (pk INTEGER PRIMARY KEY, food VARCHAR(64), points DOUBLE, name_id INTEGER)
pk food points name_id
1 food1 12.0 1
2 food2 9.0 1
3 food3 5.0 1
4 food4 15.0 2
5 food5 14.0 2
6 food6 12.0 3
Table#3(activities)
CREATE TABLE activities (pk INTEGER PRIMARY KEY, activity DOUBLE, activity_id INTEGER)
pk activity activity_id
1 5.0 1
2 4.0 1
3 2.0 2
4 4.0 3
Subselects..
MIN(26.0-20, 9.0) = 6.0
MIN(29.0-20, 2.0) = 2.0
Final Result...
6.0 + 2.0 = 8.0
RE: multiple table query with MIN()
What's the difference between pk and xxx_id? You typically have only one instance of each id, which usually is the pk.
And what does "name_id IN( SELECT pk FROM dates" mean? Name_id seems to be some kind of food, and pk a date...
RE: multiple table query with MIN()
mysql used to support something like this in a very early version, but i believe they dropped it after version 3.22 and went with the standard, which is the LEAST function
perhaps you should be asking this question in the forum for your particular database system, which i don't think you mentioned
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: multiple table query with MIN()
RE: multiple table query with MIN()
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: multiple table query with MIN()
name_id is a column which associates that row's food to a particular date. For ex... food1, food2, food3 have a name_id of 1 so therefore those three foods are connected to the date with a pk of 1 (05062009).
I guess it would be more readable if it was called date_id.
...WHERE name_id IN(SELECT pk FROM dates) means that whatever is before the WHERE clause is added to the result set if the value of name_id for that particular row is contained in the subset "SELECT pk FROM dates"...
Anyway, I was able to finally get it with the help of someone.. If you were wondering what i was getting at, this query gives me the results i need (as well as the subset results)...
CODE
(SELECT dates.pk AS fd, SUM(points) AS fp
FROM dates
JOIN foods ON name_id = fd
WHERE dates.weekly=1
GROUP BY fd
HAVING fp >= 20)
JOIN
(SELECT dates.pk AS ad, SUM(activity) AS ap
FROM dates
JOIN activities ON activity_id = ad
WHERE dates.weekly=1
GROUP BY ad)
ON fd = ad
Thanks for taking the time to help out!!