Questions related to SQL queries

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

Questions related to SQL queries

Author

Topic

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 07:49:21

Good morning,
i have four tables

Student(iid , lname, address)
Teacher(tid, lname, salary, d_number)
Lesson_teach(tid,les_name)
Lesson_attend(iid,les_name)

Well, I'm trying to find the teachers who teach one lesson and more (e.g. only if sm teaches >= 2 lessons )

I do:

select teacher.lname
from teacher,lesson_teach
where teacher.tid = lesson_teach.tid
group by lesson_teach.tid
having count(*) >= 2)

Thanks,in advance!

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 07:51:05

What do i do wrong?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 07:51:17

So what is the problem?

select teacher.lname
from teacher,lesson_teach
where teacher.tid = lesson_teach.tid
group by lesson_teach.tid
having count(*) >= 2)

Remove the last extra bracket.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:53:39

What about the last paranthesis?

SELECT t.tid, lname
from teacher t
inner join lesson_teach lt on lt.tid = t.tid
group by t.tid, lname
having count(*) >= 2

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:54:02

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 07:57:51

Well, the parenthesis was a pasting error by me.
I have a problem with group by but i dunno what

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 07:58:57

Lucky again !!

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:02:38

Did you try any of the suggestions given to you?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:03:06

Harsh, your bot is faster than my bot

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 08:06:57

Well i have studied a tutorial about group by,count(*) but it has confused me rather than helped me.

what is bot?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 08:08:25

g_p,

Post some sample data and expected output and also what problem currently you are facing.

Peter,

Yes, I recently added new plug-in in my bot called "SuperPoster Peso ver. 8.63.0001"

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:09:50

Darn! I am still using version 1.0

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 08:12:54

Well i use Oracle

ERROR at line 1:
ORA-00979: not a GROUP BY expression

This means that:
Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

I'm confused ....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:17:48

Did you try Harsh's suggestion?
Did you try my suggestion?

SELECT t.tid, lname
from teacher AS t
inner join lesson_teach AS lt on lt.tid = t.tid
group by t.tid, lname
having count(*) >= 2

What did they provide?

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 08:19:11

You need an aggregate on teacher.lname ... just like the error message says.

This is a Microsoft SQL Server forum.

Jay
to here knows when

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 08:20:15

Ah! Now I see the problem. You select teacher.LName but grouping on teach_id...that's the problem. Try Peter's suggestion...it must work!

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 09:57:07

>>Well i use Oracle

www.DBForums.com
www.ORAFAQ.com

Madhivanan

Failing to plan is Planning to fail

g_p
Starting Member

48 Posts

Posted - 2006-12-05 : 10:16:45

Well,
I have problems with the name of teacher table,i have renamed it to teacher1,teac and many other names but it says invalid identifier.
I had that error many times but when i was changing the table name it worked,but now seems that sthg else goes wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 10:22:06

If you decide to change the table names, you must also change every query built on those tables...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 10:26:56

quote:
Originally posted by g_p

Well,
I have problems with the name of teacher table,i have renamed it to teacher1,teac and many other names but it says invalid identifier.
I had that error many times but when i was changing the table name it worked,but now seems that sthg else goes wrong.

Did you read my first reply?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources