Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: Need help on a SQL Query

    hello all

    I am desperately in need of help on solving a query, It might be simple but I am desperate.

    I have two tables with the following fields

    tbl_StudentInfo (st_ID,st_Name,st_PhNo,st_EMail)
    tbl_REcptLog (r_ID,r_SID ,r_UnitCode,r_Notes,r_RecvdOn)
    and r_SID is the foreign Key

    I would like an final output of a query to have the following fields retreived

    select the r_ID, r_SID, st_NAme, r_Notes and r_RecvdOn from the data available in the two tables.

    Can someone help.

    thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Sundarss

    Welcome to the forum

    What have you come up with so far?

    Is this an assignment? If so - do you have any more details?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    6

    thanks for your response

    this is part of a task given in my short course with databases

    I have worked out most of the simpler queries but I am stuck here where I have to get some information from another table.

    I tried something like

    select * from tbl_recptLog where r_UnitCode='MIS' innerJOIN (select st_Name from tbl_StudentInfo where st_ID=tbl_recptLog.r_SID)

    i know this is not working but cannot think of a way out of it either.

    your help in geting the query across would be appreciated.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Sundarss

    The query is actually much simpler than you think.

    You were actually pretty close with your natural english description.
    Quote Originally Posted by Sundarss
    select the r_ID, r_SID, st_NAme, r_Notes and r_RecvdOn
    from the data available in the two tables.
    Each line is one clause and you have got the clause keyword correct. So - how do you think your first line (clause) should read? You can refer here:
    http://www.w3schools.com/sql/sql_select.asp
    Once you get the first line sorted the more complex second clause can be tackled.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Posts
    6
    thakns for guiding me on this, is the first part going to be like

    select r_ID,r_SID,tbl_student.st_Name,r_Notes,r_RecvdOn from tbl_RecptLog where r_UnitCode='MIS' - i know the second part follows this but I am not able to crack it after much efforts

    could you help me out...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good - the SELECT clause is fine now.

    Now - where does the 'MIS' bit come from? It isn't mentioned in your first post.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2006
    Posts
    6
    I need to Pick up the students who have passed a unit called MIS and table tbl_RecptLog contains the student ID along with the unit code on it.

    The problem is i need to print a report on this details and the name of the student needs to be retrieved from another table, thats the reason I have this PK FK relationship on the tables.

    I hope i have made the ambiguity clear now.

    thanks for the continued support anyway and nice to hear I have crossed half the barrier

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - you will now need a third clause

    Disregard any filtering of data for now - we will do that last. SQL statements follow a strict order. You specify the columns first and then specify where you are getting the data from.

    So - your data is contained in two related tables. Have a look here for your next clause (btw skip down to "Using Joins" - the section before that is a bit naughty as it is not ANSI compliant):
    http://www.w3schools.com/sql/sql_join.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2006
    Posts
    6
    thanks for the lead and getting a hang of whats happening now - hope i eventualy get there

    here is my attempt

    SELECT tbl_receiptLog.r_ID, tbl_receiptLog.r_UnitCode, tbl_receiptLog.r_recvd_On, tbl_studentInfo.student_Name
    FROM tbl_receiptLog INNER JOIN tbl_studentInfo ON tbl_receiptLog.r_Stu_ID=tbl_StudentInfo.student_ID ;

    I have this working with out the filter on, appreciated but I continued to put the filter on this way

    SELECT tbl_receiptLog.r_ID, tbl_receiptLog.r_UnitCode, tbl_receiptLog.r_recvd_On, tbl_studentInfo.student_Name
    FROM tbl_receiptLog where tbl_receiptLog.r_unitCode-'MIS' INNER JOIN tbl_studentInfo ON tbl_receiptLog.r_Stu_ID=tbl_StudentInfo.student_ID ;

    this one failed

    i think i am improving

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sundarss
    i think i am improving
    Indeed you are.

    Ok - here's the anatomy of a SQL statement:
    SELECT...
    FROM....
    [WHERE]
    [GROUP BY]...
    [HAVING]...
    [ORDER BY]....

    Clauses in square brackets are optional. These statements must always appear in this order. You can't mix and match (at least not in the way you are doing - you can sort of nest them but that is for another day ).

    So - your first statement looks great. Your second statement has clauses in the wrong order. So....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2006
    Posts
    6
    did i strike it right this time, I have it working

    SELECT tbl_receiptLog.r_ID, tbl_receiptLog.r_UnitCode, tbl_receiptLog.r_recvd_On, tbl_studentInfo.student_Name
    FROM tbl_receiptLog INNER JOIN tbl_studentInfo ON tbl_receiptLog.r_Stu_ID=tbl_StudentInfo.student_ID
    WHERE tbl_receiptLog.r_unitCode='MIS';

    I do appreciate your efforts, you were excellent. You made me learn than helping me solve the problem - I realy appreciate this effort of yours.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sundarss
    I do appreciate your efforts, you were excellent. You made me learn than helping me solve the problem - I realy appreciate this effort of yours.
    Sniff... it makes it all worth while

    Glad you are sorted & (crucially) learned something along the way.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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