1:m query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

1:m query problem

Author

Topic

ncp905
Starting Member

4 Posts

Posted - 2009-02-24 : 22:37:11

I have a parent table called committe and child table called member. My assignment question is asking for the committee that has a budget over 15000.00 and between 5 and 10 members. Here's what I think should make the problem work :

SELECT comName FROM committee,member
WHERE member.comID = committee.comID GROUP BY comName HAVING comBudget >15000.00 AND COUNT(memLast) BETWEEN 5 AND 10;

I tried switching the order of the 5 and 10 condition and >15000 and I keep getting an unknown 'comBudget' in 'having clause'. Yet I joined the tables together. Does anyone know the proper code? It's part of an assignment that I have to hand it in tomorrow.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 22:41:40

SELECT comBudget,comName FROM committee,member
WHERE member.comID = committee.comID GROUP BY comName,comBudget HAVING comBudget >15000.00 AND COUNT(memLast) BETWEEN 5 AND 10

matty
Posting Yak Master

161 Posts

Posted - 2009-02-24 : 23:32:28

SELECT comName
FROM committee,member
WHERE member.comID = committee.comID AND comBudget >15000.00
GROUP BY comName
HAVING COUNT(memLast) BETWEEN 5 AND 10

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-25 : 01:15:10

try this
SELECT comName
FROM committee c inner join member m on m.comID = c.comID AND comBudget >15000.00
GROUP BY comName
HAVING COUNT(memLast) BETWEEN 5 AND 10
use inner join than cross join

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:14:20

quote:
Originally posted by ncp905

I have a parent table called committe and child table called member. My assignment question is asking for the committee that has a budget over 15000.00 and between 5 and 10 members. Here's what I think should make the problem work :

SELECT comName FROM committee,member
WHERE member.comID = committee.comID GROUP BY comName HAVING comBudget >15000.00 AND COUNT(memLast) BETWEEN 5 AND 10;

I tried switching the order of the 5 and 10 condition and >15000 and I keep getting an unknown 'comBudget' in 'having clause'. Yet I joined the tables together. Does anyone know the proper code? It's part of an assignment that I have to hand it in tomorrow.

which table does comBudget contain? also are you looking for individual value or sum of comBudget to be > 15000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources