multiple table query with MIN()

freddyD · May 3, 2009

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

JarlH · May 4, 2009

Does your MIN function really take two arguments???

LKBrwnDBA · May 6, 2009

1) You need to format and enclose your queries in "code" tags to make them readable.
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:

HAVING foods, name_id, foods activities (
  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

freddyD · May 6, 2009

Thanks for the reply!!
Sorry about not providing a sample table. You're right, it would make things a lot easier.
Anyway...

Code:

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 looking at the query from my original post (one that doesn't work), i would be looking for a result set containing one value..8.0

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

JarlH · May 7, 2009

I find this table design quite confusing.

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

r937 · May 7, 2009

your use of the "MIN" function is non-standard (and this is the ANSI SQL forum)

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

JarlH · May 7, 2009

Hey r937, are you sure about LEAST as an ANSI SQL defined function? I searched the SQL-2008 specification for LEAST, but found nothing.

r937 · May 7, 2009

hey jarlh, yes, LEAST and GREATEST are in SQL-2003 (according to Joe Celko's SQL For Smarties, 3rd edition)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

freddyD · May 7, 2009

JarlH,
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 SUM(MIN(fp-20, ap)) FROM
  (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!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

multiple table query with MIN()

freddyD

Programmer

JarlH

Programmer

LKBrwnDBA

MIS

freddyD

Programmer

JarlH

Programmer

r937

Technical User

JarlH

Programmer

r937

Technical User

freddyD

Programmer

Similar threads

Part and Inventory Search

Sponsor