Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2004
    Posts
    78

    Unanswered: simple selct query problem

    hi guys go easy on me as i'm a newby., but i am having problems with the following query.

    "Find the members of the program committee who have reviewed three or more papers so far."

    here are the table that i have

    Review_table(Review_id(PK), Reviewer_id(FK) Relevance, Originality, Technical Merit, Overall Mark)

    Reviewers_table(Reviewer_id(PK), First_name, Surname, Email, Phone)


    this is what i have tried so far but it does not work

    SELECT *
    From reviewers_table r , review_table t
    where count(t.Reviewer_ID) >3


    i get the following error

    ERROR at line 3:
    ORA-00934: group function is not allowed here

    can n e 1 see where i am going wrong

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is this classwork? If it is, the forum has a policy of not doing homework. You will never learn if your answers are handed to you.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2004
    Posts
    78
    well it's sort of class work. i'm a college student who is doing a internship for a year and was just going through 1 of my oracle college books and trying some of the exercises

    CAN n e 1 help?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Ok, try this.

    You didn't have the relationship between the two tables defines and you have to use the having clause.

    SELECT r.first_name,r.surname
    From reviewers_table r , review_table t
    where r.Reviewer_id = t.Reviewer_id
    group by r.first_name,r.surname
    having count(*) > 3;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    I agree with Bill, and will only tell you that even if you didnt get that error, you would not get the data you are expecting.

    Go back to the beginning and start with the basics...Remember for every table you add, you need a way to link them together...
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by ss659
    I agree with Bill, and will only tell you that even if you didnt get that error, you would not get the data you are expecting.

    Go back to the beginning and start with the basics...Remember for every table you add, you need a way to link them together...
    I agree with you too, but I helped him because from his discription, he didn't have an instructor to ask. However evilz35, if you got through an oracle class, and can't code such a simple select, you really need to go back to basics and start over.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Feb 2004
    Posts
    78
    thanks for that - that looks about right. it was the having clause that threw me. looks like i need to look that up and revise it.

    cheers

  8. #8
    Join Date
    Feb 2004
    Posts
    78

    another query problem

    hi people i am also having problems with the following query. can n e 1 help. i have searched high and low through my oracle books on how to do this but can't figure it out.

    what i have done is written a select query so that for each paper i list the paper’s title, the author(s) name(s) and the paper number from the following tables.


    Paper_table (Paper_no(PK), Topic_id(FK), Paper_title, Decision, Copyright_OK, Received_OK, Length_OK, Format _OK)

    Author_table (Author_id(PK), First_name, Surname, Email , Phone)

    Author_paper (Author_id(PK,FK), Paper_no(PK,FK))


    this i what i have got so far, it looks correct but if there was more that 1 author for a paper then this query would repeat the paper title. how can i update this so that it only lists a paper once with all of it's authors.

    select p.Paper_no, p.Paper_title, a.Surname
    from paper_table p, author_table a, Author_paper c
    where a.author_id = c.author_id
    and p.paper_no = c.paper_no

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That is not something you would do in the SQL, but in the application. For example, using SQL Plus's BREAK commands:

    BREAK ON paper_no ON paper_title

  10. #10
    Join Date
    Feb 2004
    Posts
    78
    thanks tony - i was pulling my hair out trying to figure out where imight be going wrong.

  11. #11
    Join Date
    Feb 2004
    Posts
    78
    can n e 1 check that i have done the following query correctly please.

    i have to use the following tables to find the titles of papers reviewed by BOTH mr john and mr smith

    Paper_table (Paper_no(PK), Topic_id(FK), Paper_title, Decision, Copyright_OK, Received_OK, Length_OK, Format _OK)

    Review_table(Review_id(PK), Reviewer_id(FK) Relevance, Originality, Technical Merit, Overall Mark)

    Review_paper (Review_id(PK,FK), Paper_no(PK,FK))

    Reviewers_table(Reviewer_id(PK), First_name, Surname, Email, Phone)

    this is what i have got so far.

    select p.Paper_title, y.surname
    from paper_table p, Review_table r, Review_paper x, Reviewers_table y
    where r.review_id = x.review_id
    and p.paper_no = x.paper_no
    and y.reviewer_id = r.reviewer_id
    and y.surname like 'smith'
    and y.surname like 'john'

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, since you are looking for 2 reviewers you need to join to the Review and Reviewers tables twice with different aliases (e.g. r1, r2, y1, y2).

    At the moment you are trying to find one review who is simultaneously called 'Mr Smith' and 'Mr John'!

    (By the way, "n e 1" isn't much easier or shorter to type than "anyone", and it is a lot harder to understand!)

  13. #13
    Join Date
    Feb 2004
    Posts
    78
    thanks for that tony , but i'm not sure what you mean.

    how do you create 2 alias's for 1 table?

  14. #14
    Join Date
    Feb 2004
    Posts
    78
    do you mean like this

    select p.Paper_title, y1.surname, y2.surname
    from paper_table p, Review_table r1, Review_paper x, Reviewers_table y1, Review_table r2, Reviewers_table y2
    where r1.review_id = x.review_id
    and r2.review_id = x.review_id
    and p.paper_no = x.paper_no
    and y1.reviewer_id = r1.reviewer_id
    and y2.reviewer_id = r2.reviewer_id
    and y1.surname like 'john'
    and y2.surname like 'smith'
    Last edited by evilz35; 03-07-05 at 08:28.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Exactly! Indeed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •