I need data in this format:
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
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.
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.
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
WHERE (child.parentID <> 0) AND (Student.studentID = 1)
ORDER BY parent.name
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.
, COALESCE(parent.name + ' (','') + child.name
+ CASE WHEN parent.name IS NULL THEN '' ELSE ')' END AS course
ON OwedFees.studentID = Student.studentID
ON CourseFees.courseFeesID = OwedFees.couseFeesID
ON PaymentPlan.paymentPlanID = CourseFees.paymentPlanID
JOIN Courses AS child
ON child.courseID = CourseFees.courseID
JOIN Courses AS parent
ON parent.courseID = child.parentID
WHERE Student.idNumber = @id
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???
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.
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.
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.