freddyD freddyD - 2 months ago 6
SQL Question

Using Multiple Tables, Multiple Values with Min() SQL

I am using SQLite in an application that I am developing.

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 this is where 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 would work if there was no syntax whatsoever (lol!). This doesn't work but i just wanted to show it to better understand what I'm trying to do.

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));


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 though 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.

How can I get something like the above query I created to work?

EDIT...some example tables to better help. Thanks jellomonkey and hainstech

Table#1(dates)

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


With this ex and query from my original post (one that doesn't work), I would be looking for a result set containing one value..8.0

MIN(26.0-20, 9.0) = 6.0

MIN(29.0-20, 2.0) = 2.0

6.0 + 2.0 = 8.0

I hope this helps!

Answer

After much head-scratching and divining I suspect what you want might be:

SELECT SUM(MIN(fp-20, ap)) FROM
  (SELECT dates.pk AS fd, SUM(points) AS fp
  FROM dates
  JOIN foods ON name_id = fd
  GROUP BY fd
  HAVING fp >= 20)
    JOIN
  (SELECT dates.pk AS ad, SUM(activity) AS ap
  FROM dates
  JOIN activities ON activity_id = ad
  GROUP BY ad)
    ON fd = ad

The column names appear to have zero connection to their meaning but, hey, at least this does give 8.0 and the subselects give the other numbers you mention!-)

Comments