×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

multiple table query with MIN()

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

RE: multiple table query with MIN()

Does your MIN function really take two arguments???

 

RE: multiple table query with MIN()

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 ?
3eyes

 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

RE: multiple table query with MIN()

(OP)
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
 

RE: multiple table query with MIN()

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

RE: multiple table query with MIN()

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

RE: multiple table query with MIN()

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

RE: multiple table query with MIN()

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

RE: multiple table query with MIN()

(OP)
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!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close