I have an unusual group by and need help

Code Examples Summary / Group By / Totalizations
#1

Hi All,

I am relatively new to MySQL and I have come across a situation that I am not sure how to deal with it. The project is for a bookkeeping system.

I have a table called ledger_codes, and it has a PK of an integer that is auto incremented. I also have a column that records the actual ledger code, but is not auto incremented as the user sets the value.

I also have a table of transactions. One of the columns records the link to a ledger code. It is linked to the PK field in the ledger code table.

When I do a “group by” of these transactions, I need to group the records by the ledger code field, and not the PK field as is recorded in the transactions database.

I am not sure how to do this.

I can do it by the linking code easily enough, but not sure how to do it by another field. Do I do some join in the SQL statement? I am not sure how SC handles joins etc.

Thanks

Tony

#2

I have experimented with an inner join and it works in the MySQL engine. Below is the SQL SELECT statement in the grid SQL setting. As I said, it returns the correct data and the correct sorting.

SELECT
ta_id,
ta_tran,
ta_date,
ta_ref,
ta_glcode,
ta_amount,
ta_gst,
ta_net,
ta_drcr,
ta_bastype,
ta_note,
ta_type,
gl_code
FROM
allocations
INNER JOIN
ledger_codes ON allocations.ta_glcode = ledger_codes.gl_id
WHERE
ta_date BETWEEN ‘2014-07-01’ AND ‘2015-06-30’
ORDER BY
gl_code, ta_date

However, it does not work in SC. I get an error on compile that states that the “Search fields undefined”

Any idea what I am doing wrong?

Thanks

Tony

#3

SOLVED - I have an unusual group by and need help

I have managed to solve this by using an inner join, but BEWARE changing your SQL statement with a join can turn the whole application into a dogs breakfast.

The search fields undefined error turned to grid fields undefined error, and then the edit fields setting truncated the table name on the id field and would not save the fields to be displayed.

In the end, I added a single character alias in the SQL statement, one for each table, I did this because a.id_field works better than allocations.id_field (truncation issue noted above).

Then you have to change all of the links and code in events to match adding the alias.

It then worked fine.

Tony

#4

[QUOTE=fiscal;35494]I have experimented with an inner join and it works in the MySQL engine. Below is the SQL SELECT statement in the grid SQL setting. As I said, it returns the correct data and the correct sorting.

SELECT
ta_id,
ta_tran,
ta_date,
ta_ref,
ta_glcode,
ta_amount,
ta_gst,
ta_net,
ta_drcr,
ta_bastype,
ta_note,
ta_type,
gl_code
FROM
allocations
INNER JOIN
ledger_codes ON allocations.ta_glcode = ledger_codes.gl_id
WHERE
ta_date BETWEEN ‘2014-07-01’ AND ‘2015-06-30’
ORDER BY
gl_code, ta_date

However, it does not work in SC. I get an error on compile that states that the “Search fields undefined”

Any idea what I am doing wrong?

Thanks

Tony[/QUOTE]

I’ve seen search fields undefined come by quite often, quite certain for me it’s just been whenever I change my SQL it empties the fields it knows for the advanced search so you have to set them again. (think it’s a bug…)
Far as giving the tables alias’s I’d normally suggest it anyways, makes it a bit easier to read in general.