Tool to create new query

General
1

Hi folks,

I guess, most of us create queries manually directly in phpmyadmin. However, I’m kind of stuck with a new query that I have to build for the billing module.

Is there a tool out there, maybe some kind of assistant or wizard that could help in the creation of new queries?

What do you use if a query becomes a little bit more complicated?

Kind regards,
Roman

2

Roman, I find using semantic indentation (this is a term I use, but not sure if others do) can help to make complex queries easier to read and understand…but beyond that I’ve not seen a tool that assists in some way.

Can you tell us more about the query in question…we might be able to help.

Ross

3

Hi Ross,

Thank you for your offer!

School administration needs a simple and fast tool without going through spreadsheet to know if all enrolled students have an invoice for each billing schedule.

The problem is that some students didn’t get a bill and some got more than one. I thought it would be easy to create a query using count showing zero or null for students not having a bill and a value greater than 1 if they have more than one bill by mistake.

The list should contain all billing schedules (Ad Hoc bills are not important at this point) in the current year and each enrolled student incl. left ones, e.g. with 300 students and 3 bills in term 1 resulting in a list of exactly 900 records.

I don’t really understand why I can’t make this work.

Kind regards,
Roman

4

Hi Ross,

I finally figured out a way. I guess it’s a bit messy but works just fine for me. In case somebody needs something similar. Here we are:

SELECT gibbonPerson.gibbonPersonID, nameShort AS streamBatch, surname, firstName, gibbonPerson.status AS currentStatus, dateEnd AS leftDate, gibbonFinanceBillingSchedule.name AS billingSchedule, count(gibbonFinanceInvoice.gibbonFinanceInvoiceID) AS scheduleCount FROM gibbonFinanceBillingSchedule JOIN gibbonStudentEnrolment ON (gibbonFinanceBillingSchedule.gibbonSchoolYearID=gibbonStudentEnrolment.gibbonSchoolYearID AND gibbonStudentEnrolment.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’)) JOIN gibbonPerson ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID) JOIN gibbonRollGroup ON (gibbonStudentEnrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID) JOIN gibbonFinanceInvoicee ON (gibbonStudentEnrolment.gibbonPersonID=gibbonFinanceInvoicee.gibbonPersonID) LEFT JOIN gibbonFinanceInvoice ON (gibbonFinanceInvoicee.gibbonFinanceInvoiceeID=gibbonFinanceInvoice.gibbonFinanceInvoiceeID AND gibbonFinanceInvoice.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’) AND gibbonFinanceInvoice.gibbonFinanceBillingScheduleID= gibbonFinanceBillingSchedule.gibbonFinanceBillingScheduleID AND gibbonFinanceInvoice.status<>‘Pending’ AND gibbonFinanceInvoice.status<>‘Cancelled’ AND gibbonFinanceInvoice.status<>‘Refunded’) WHERE gibbonFinanceBillingSchedule.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’) GROUP BY gibbonPerson.gibbonPersonID, billingSchedule ORDER BY billingSchedule, surname, firstName

Kind regards,
Roman

5

Roman,

Nice work. Does not have to be perfect, as long as it works! If you look at the attached you’ll see I have added indents to make it easier to read.

I don’t have time to look now, but I think you could make this more efficient by taking the following:

JOIN gibbonStudentEnrolment ON (gibbonFinanceBillingSchedule.gibbonSchoolYearID=gibbonStudentEnrolment.gibbonSchoolYearID AND gibbonStudentEnrolment.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’))

and using a separate JOIN to link to gibbonSchoolYear and then checking for Current in WHERE. There is another similar case in there too. Something to consider!

Thanks for sharing : ))

Ross

6

Thanks Ross!