Sql 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

Sql query problem

Author

Topic

g_p
Starting Member

48 Posts

Posted - 2006-12-10 : 07:27:34

Good evening!

I'm trying to find the sudents who attend all the lessons of the professor whose tid = 12 (in relational algebra we use division for this query cause we have "all")

I do :

select Student.lname
from Student,Lesson_attend
where Student.iid = Lesson_attend.iid and Lesson_attend.lno = all(select Lesson_teach.lno
from Lesson_teach,Teacher
where Teacher.tid = Lesson_teach.tid and Teacher.tid= 2)

My tables are :

Student(iid , lname, address)
Teacher(tid, lname, salary, d_number)
Lesson_teach(tid,les_name,lno)
Lesson_attend(iid,les_name,lno) lno is lesson number

The output mest be only one Student,cause in my insert.sql file there is only one Student who attends all of his lessons.

I get :no rows selected

What have i done wrong and why don't I get the desirable output?

Thanks,in advance :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-10 : 10:15:44

It would be helpful if you could post some sample data along with expected output.

Maybe this could be what you are looking for:

select Student.lname
from Student Join Lesson_attend
on Student.iid = Lesson_attend.iid 
join Lesson_teach on Lesson_attend.lno = Lesson_teach.lno
join Teacher on Teacher.tid = Lesson_teach.tid
where Teacher.tid= 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

g_p
Starting Member

48 Posts

Posted - 2006-12-10 : 15:09:07

Thanks for replying :)
I did that,but again i get the output no rows selected.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 15:32:27

Try this

SELECT		q.*
FROM		(
			SELECT		la.iid,
					SUM(CASE WHEN lt.lno = la.lno THEN 1 ELSE 0 END) attend
			FROM		LessonTeach lt
			CROSS JOIN	LessonAttend la
			WHERE		lt.tid = 12
			GROUP BY	la.iid
		) q
WHERE		q.attend = (SELECT COUNT(*) FROM LessonTeach WHERE tid = 12)

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-10 : 17:46:23

GP,

Is the actual TID = 12 or 2? The description says 12... the code posted on the same request says 2. I'm thinking the devil's in the data and you need to find our for sure what you're actually looking for.

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 17:48:50

No way Jeff! It can't possible be that easy, right?

Now I learnt that it is worth to proof-read sample query...

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-10 : 18:24:57

Hi guys,Thank you all :)

Finally it worked following your advice :)

The actual tid was 2, it's just that i wroted it wrong in the first sentence when i was typing it for the forum.

Well,now I'm trying to find all the teachers who teach exactly three lessons

I do:

select Teacher.lname
from Teacher,Lesson_teach
where Teacher.tid = Lesson_teach.tid and Lesson_teach.lno = all(select count(Lesson_teach.lno)
from Teacher,Lesson_teach
group by Lesson_teach.lno
having count(Lesson_teach.lno) = 3)

Well in my output,
I get the lnames of teachers twice (if they teach two lessons),truple (if they teach three lessons) or once if they teach only one lesson.

What do i do wrong in my SQL?

I use "having count(...) = 3" as a constraint in order to take the lno of those lessons who are taught by one teacher and they amount to 3.

Thanks, in advance :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-10 : 18:28:33

[code]select t.*
from teacher t
inner join (
select tid
from lesson_teach
group by tid
having count(*) = 3
) q on q.tid = t.tid[/code]

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-11 : 09:10:36

Thanks a lot, it worked! :)

Well, I'm going to make a question that has nothing to do with this forum and with Sql,
it's just that I'm searching sthg.

Do you know any link where i can find an algorithm which takes as output a float number like 345.67 and converts it to a string following some steps?

Thanks,in advance :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 09:17:02

Use STR function, or CAST/CONVERT.

Peter Larsson
Helsingborg, Sweden

g_p
Starting Member

48 Posts

Posted - 2006-12-11 : 09:55:16

Well, i have to program this in C language so I have to find an algorithm for this, but
i haven't found anything yet

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-11 : 13:48:44

in C, you could just use standard routines such as fprintf to convert a float to a stream.

http://www.cplusplus.com/ref/cstdio/fprintf.html

but this forum is not really the place for such questions.

http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources