Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: joins query

    Hi,

    I have some tables as thus:

    Student
    --------
    studentID----firstname----surname---idNumber

    Courses
    --------
    courseID-----name----

    OwedFees
    ----------
    owedFeesID----studentID----owedFees

    CourseFees
    -----------
    courseFeesID----paymentPlanID

    paymentPlan
    ------------
    paymentPlanID-----name----installments

    I need data in this format:
    CourseName---owedFees----name----installments

    My sql:
    SELECT PaymentPlan.name, PaymentPlan.installments, Courses.name AS Expr1
    FROM Courses INNER JOIN
    CourseFees ON Courses.courseID = CourseFees.courseID LEFT OUTER JOIN
    PaymentPlan ON CourseFees.paymentPlanID = PaymentPlan.paymentPlanID CROSS JOIN
    Student
    WHERE (Student.idNumber = @idNumber)

    is giving me all the rows in the paymentPlan table. I want to know how to get the name and installments for the paymentPlanID in the OwedFees table only where the studentID = the one chosen by IDNumber. Thanks.

  2. #2
    Join Date
    Jul 2009
    Posts
    168

    Coalesce subquery

    Hi all,

    I am having a query with inner joins as this:
    Code:
    SELECT        PaymentPlan.name, PaymentPlan.installments, OwedFees.owedFees, Courses.name AS Expr1, CourseFees.amount, OwedFees.owedFeesID, 
                             Student.studentID
    FROM            Student INNER JOIN
                             OwedFees ON Student.studentID = OwedFees.studentID INNER JOIN
                             CourseFees ON OwedFees.couseFeesID = CourseFees.courseFeesID INNER JOIN
                             PaymentPlan ON CourseFees.paymentPlanID = PaymentPlan.paymentPlanID INNER JOIN
                             Courses ON CourseFees.courseID = Courses.courseID
    WHERE        (Student.idNumber = @id)
    For the courses table:
    Courses
    --------
    - courseID (PK)
    - name
    - parentID
    - courseCodeID (FK)

    eg is :
    CourseID------name----------------parentID-------courseCodeID
    1----------Bachelor in Commerce------0-----------------1
    2----------Marketing Management-----1-----------------2
    3----------Human Resource Mgtm------1-----------------3

    In my above query how do I construct the course name? Any idea? Thanks.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo View Post
    In my above query how do I construct the course name?
    sorry, i do not understand what you mean by "construct"

    the course name is already in your query, and it has the column alias of "Expr1" --
    Code:
    SELECT ... Courses.name AS Expr1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    Yes sorry about that. I meant the query will not give me the proper results because I have to concatenate the course names with regards to the parentid in the courses' table. How can I do that. I think it was you who gave me the following sql where you could concatenate the names and it works but I want to include that coalesce query in the above query to get the correct course name for the student. I hope am being clearer. Thanks.

    Code:
    SELECT        COALESCE (child.courseID, parent.courseID) AS dropdown_index, parent.name + COALESCE (' (' + child.name + ')', '') AS dropdown
    FROM            Courses AS parent LEFT OUTER JOIN
                             Courses AS child ON child.parentID = parent.courseID CROSS JOIN
                             Student
    WHERE        (child.parentID <> 0) AND (Student.studentID = 1)
    ORDER BY parent.name

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand, sorry

    you're joining Student to OwedFees to CourseFees to PaymentPlan to Courses

    where does "the correct course name for the student" come from, if not the Courses table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2009
    Posts
    168
    Sorry about the confusion, let me try to clarify with the data itself.

    Courses
    --------
    - courseID (PK)
    - name
    - parentID
    - courseCodeID (FK)

    Code:
    INSERT INTO Courses VALUES ( Bachelor of Commerce , NULL , 3 );
    INSERT INTO Courses VALUES ( Bachelor of Public Administration , NULL , 1 );
    INSERT INTO Courses VALUES ( Bachelor of Business Administration, NULL , 2 );
    INSERT INTO Courses VALUES ( Marketing Management , 1 , 3 );
    INSERT INTO Courses VALUES ( Human Resource Management , 1 , 3 );
    This code links to one of the threads you replied about putting the data above in a drop down:
    http://www.dbforums.com/mysql/165480...e-columns.html. It works fine but what I would require now is I need to construct the course name, as from above, say where name is 'Human Resource Management', so the course becomes 'Bachelor of Commerce (Human Resource Management)'. A concatenation is required to form the whole course name you see. Any advice? Thanks.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT PaymentPlan.name
         , PaymentPlan.installments
         , OwedFees.owedFees
         , COALESCE(parent.name + ' (','') + child.name 
              + CASE WHEN parent.name IS NULL THEN '' ELSE ')' END AS course
         , CourseFees.amount
         , OwedFees.owedFeesID
         , Student.studentID
      FROM Student 
    INNER
      JOIN OwedFees 
        ON OwedFees.studentID = Student.studentID
    INNER
      JOIN CourseFees 
        ON CourseFees.courseFeesID = OwedFees.couseFeesID
    INNER
      JOIN PaymentPlan 
        ON PaymentPlan.paymentPlanID = CourseFees.paymentPlanID
    INNER
      JOIN Courses AS child
        ON child.courseID = CourseFees.courseID
    LEFT OUTER 
      JOIN Courses AS parent 
        ON parent.courseID = child.parentID
     WHERE Student.idNumber = @id
    Last edited by r937; 06-07-10 at 08:52.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you have some design issues. It doesn't look like you have all the relations defined properly between your tables. Also, why would you have an OwedFees table? You should be figuring outstanding fees from the other tables you have described. Lastly, with your SQL statement, you are looking for Student.idNumber = @idNumber, where is the Student table in your SQL???
    Dave

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps we should ask a moderator to merge this thread with this one -- http://www.dbforums.com/microsoft-sq...-subquery.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2009
    Posts
    168
    Hi r937. It works perfect! Many thanks for your help and advice. For the sake of information, could you tell us the secret of building these complex statements, like how we should proceed to build such a statement. How we should start building small parts of the query and test it and then building on the query further. Take the query you gave as an example. Thumbs up if you can do that. Thanks.
    Last edited by kpeeroo; 06-07-10 at 11:41.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's nothing difficult about it -- just do like you said, start with one table, test the query, add a join to the second table, test again, add a third table, and so on

    it helps to understand how the tables are related, which columns are the foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2009
    Posts
    168
    Thanks I will try it but it does not always work great. I was thinking there might be some kind of general algorithm to follow but i will have a go like you suggested. Did your reply answer the concern about design issues for dav1mo? I mean I will need a table to store the details of who owes fees, for which course, etc.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Doesn't the payment table tell you the same thing? The payment shows you someone has paid x of a course which costs n. By subtracting the sum of x from n tells you how much is owed, does it not? By having two tables keeping track of the same item, you have to perform twice as much work within your program(s). One it is too costly for an application and two is too easy to screw something up with future maintenance.
    Dave

  14. #14
    Join Date
    Jul 2009
    Posts
    168
    Thanks for your reply and advice Dave. I think you are quite right there I might be keeping track of the same information twice and might end up redundant. Here are the two tables again:

    StudentPayment
    ----------------
    - paymentID (PK)
    - amountPaid
    - date
    - paymentMethodID (FK)
    - owedFeesID (FK)

    OwedFees
    ----------
    - owedFeesID (PK)
    - date
    - studentID (FK)
    - courseFeesID (FK)
    - examsID (FK)
    - projectsID (FK)
    - owedFees

    So these 2 tables might be merged into 1:

    StudentPayment
    ----------------
    - paymentID (PK)
    - amountPaid
    - date
    - paymentMethodID (FK)
    - studentID (FK)
    - courseFeesID (FK)
    - examsID (FK)
    - projectsID (FK)

    Is that what you are meaning right? Then I just substract amountPaid from say courseFees, and I get the owedFees. Seems fair enough to me.

Posting Permissions

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