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.
Author |
Topic |
whitepear
Starting Member
24 Posts |
Posted - 2014-04-12 : 02:48:57
|
Hello!I have upgraded last program, with pupils comming to lectures.Easch lecutre is paid particularly.I need result, how much each course earned regardign to number of pupils attending.I got this:CREATE TABLE LP(ID_LP INTEGER PRIMARY KEY,TK_ID_LECTURE INTEGER,TK_ID_PUPIL INTEGER);CREATE TABLE PUPIL(ID_PUPIL INTEGER PRIMARY KEY);INSERT INTO TOPIC VALUES(1,'PIANNO',1);INSERT INTO TOPIC VALUES(2,'VIOLA',1);INSERT INTO TOPIC VALUES(3,'BMW',2);INSERT INTO TOPIC VALUES(4,'AUDI',2);INSERT INTO TOPIC VALUES(5,'DOGS',3);INSERT INTO TOPIC VALUES(6,'CATS',3);COMMIT;INSERT INTO LECTURE VALUES(1,1,1);INSERT INTO LECTURE VALUES(2,2,2);INSERT INTO LECTURE VALUES(3,3,3);INSERT INTO LECTURE VALUES(4,4,4);INSERT INTO LECTURE VALUES(5,5,1);INSERT INTO LECTURE VALUES(6,6,2);INSERT INTO LECTURE VALUES(7,1,3);INSERT INTO LECTURE VALUES(8,2,4);INSERT INTO LECTURE VALUES(9,3,1);INSERT INTO LECTURE VALUES(10,4,2);COMMIT;INSERT INTO PRICE VALUES(1,'A',10);INSERT INTO PRICE VALUES(2,'B',20);INSERT INTO PRICE VALUES(3,'C',30);INSERT INTO PRICE VALUES(4,'D',40);COMMIT;INSERT INTO COURSE VALUES(1,'MUSIC');INSERT INTO COURSE VALUES(2,'CARS');INSERT INTO COURSE VALUES(3,'ANIMALS');COMMIT;INSERT INTO PUPIL VALUES(1);INSERT INTO PUPIL VALUES(2);INSERT INTO PUPIL VALUES(3);COMMIT;INSERT INTO LP VALUES(1,1,1);INSERT INTO LP VALUES(2,1,2);INSERT INTO LP VALUES(3,2,1);INSERT INTO LP VALUES(4,3,1);INSERT INTO LP VALUES(5,4,1);INSERT INTO LP VALUES(6,5,1);INSERT INTO LP VALUES(7,6,1);INSERT INTO LP VALUES(8,7,1);INSERT INTO LP VALUES(9,8,1);INSERT INTO LP VALUES(10,9,1);INSERT INTO LP VALUES(11,10 1);INSERT INTO LP VALUES(12,1,3);INSERT INTO LP VALUES(13,2,2 );COMMIT;SELECT NAME_COURSE, SUM(SUM_PRICE) FROM TOPIC, LECTURE, PRICE, COURSE, LP, PUPIL WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE AND PUPIL.ID_PUPIL=LP.TK_ID_PUPIL AND LECTURE.ID_LECTURE=LP.TK_ID_LECTURE GROUP BY NAME_COURSE ORDER BY NAME_COURSE DESC;And it does nto work at all.As it is my first sql program in my life, please, can somone advice me?many thanks!!! |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-04-12 : 05:00:28
|
Maybe this:select course.name_course ,sum(price.sum_price) from course left outer join topic on topic.tk_id_course=course.id_course left outer join lecture on lecture.tk_id_topic=topic.id_topic left outer join price on price.id_price=lecture.tk_id_price left outer join lp on lp.tk_id_lecture=lecture.id_lecture left outer join pupil on pupil.id_pupil=lp.tk_id_pupil group by course.name_course order by course.name_course desc |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-12 : 06:47:44
|
we mustn't use l/r join in this task... |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-04-12 : 13:19:45
|
Then use inner join:select course.name_course ,sum(price.sum_price) from course inner join topic on topic.tk_id_course=course.id_course inner join lecture on lecture.tk_id_topic=topic.id_topic inner join price on price.id_price=lecture.tk_id_price inner join lp on lp.tk_id_lecture=lecture.id_lecture inner join pupil on pupil.id_pupil=lp.tk_id_pupil group by course.name_course order by course.name_course desc |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-13 : 03:14:56
|
Hello!pROBLEM IS, We actually did not mention word "join" itself.if we join, we do it just usint a dot (.), in the row where.žplease, is there a way to do it the way like this? i tried, but online compiller does not let it go...is there my mistake?MANY THANKS!!! |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-04-13 : 07:12:55
|
I don't see anything wrong with the syntax in both my suggestions. And you lacking to include description of all the tables, gives me no way to test. Mssql server would not complain about not knowing inner join, so I suspect you're not using mssql server. If this is the case, you're in the wrong forum. |
|
|
|
|
|
|
|