without pupil and lp works, not with them??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

without pupil and lp works, not with them???

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources