Jump to content
gabucknall

Report table not producing separate rows

Recommended Posts

I have a mySQL database with some of the tables and fields being:
CLIENT - clientid, client
MATTERS - matterid, clientid, refno, nickname
MATTERJUNACTIONS - matterid, notes, fee, actiondate
EXPENSES - expensid, matterid, expensedate, amount

A client can have many matters and each matter can have many actions and a number of expenses. I want to produce a report which has a separate row for each action or expense together with matter and client details. The report column headers would be:

clientid  | client  | refno  | nickname | actiondate  | note  | fee   | expensedate   | amount  |

I have a query:

SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee
FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN  expenses ON matters.matterid=expenses.matterid 
WHERE client.clientid = 238

This produces a report with a number of rows but doesn't separate the rows with expenses on a different row to the actions. I get something like:

238  | ABC Ltd  | 1245   | matternickname | 2018/4/1   | this is the action note   | 50   | 2018/4/15   | 25   |

Rather than
238  | ABC Ltd  | 1245   | matternickname | 2018/4/1   | this is the action note   |          |                       |          | 

238  | ABC Ltd  | 1245   | matternickname |                      |                                         | 50     | 2018/4/15   | 25   |

What am I doing wrong?

Edited by gabucknall

Share this post


Link to post
Share on other sites

Your query selection does not include any columns from the expenses table, so it's strange that it outputs them. Magic maybe.

If you want the actions and expenses on separate lines, why does your required output have the action fee on the expense line?

Share this post


Link to post
Share on other sites
2 minutes ago, Barand said:

Your query selection does not include any columns from the expenses table, so it's strange that it outputs them. Magic maybe.

If you want the actions and expenses on separate lines, why does your required output have the action fee on the expense line?

Sorry - I copied the wrong piece of text. The query should be:

SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee
FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN  expenses ON matters.matterid=expenses.matterid 
WHERE client.clientid = 238

What I was saying was that I want the actions and expenses on separate lines from one query but they appear together.

Share this post


Link to post
Share on other sites
13 minutes ago, Barand said:

Your query selection does not include any columns from the expenses table

And it still doesn't

9 minutes ago, gabucknall said:

SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee

 

Share this post


Link to post
Share on other sites
7 minutes ago, Barand said:

And it still doesn't

 

SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee
FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN  expenses ON matters.matterid=expenses.matterid 
WHERE client.clientid = 238

It was there but needed scrolling right. Have removed the code tags fromat so it's all viewable.

SELECT client.clientid, client.client, matters.refno, matters.nickname, matterjuncactions. actiondate, matterjuncactions.notes, matterjuncactions.fee
FROM client INNER JOIN matters ON client.clientid=matters.clientid LEFT JOIN matterjuncactions ON matters.matterid=matterjuncactions.matterid LEFT JOIN  expenses ON matters.matterid=expenses.matterid 
WHERE client.clientid = 238

Edited by gabucknall

Share this post


Link to post
Share on other sites

I know you joined to the expenses table.

The point I was making (yet again) is that you do not select any columns from that table. Your select clause only has columns from client, matters and matterjunactions table.

Anyway, so you don't waste any more time by posting a query that could not possible produce the results that you claim, here is the query you probably need

SELECT c.clientid
     , c.client
     , m.refno
     , m.nickname
     , ae.actiondate
     , ae.notes
     , ae.fee
     , ae.expensedate
     , ae.amount
FROM client c
     INNER JOIN 
     matters m ON c.clientid = m.clientid 
     LEFT JOIN (
                SELECT matterid
                     , actiondate
                     , notes
                     , fee
                     , null as expensedate
                     , null as amount
                     , 'A' as type
                FROM matterjunactions
                UNION ALL
                SELECT matterid
                     , null
                     , null
                     , null
                     , expensedate
                     , amount
                     , 'E' 
                FROM expenses
                ) ae ON m.matterid = ae.matterid
WHERE c.clientid = 238
ORDER BY m.matterid, type, COALESCE (actiondate, expensedate);

+----------+----------+-------+----------+------------+------------------+------+-------------+--------+
| clientid | client   | refno | nickname | actiondate | notes            | fee  | expensedate | amount |
+----------+----------+-------+----------+------------+------------------+------+-------------+--------+
|      238 | Client A | A123  | Nick 123 | 2018-03-01 | This is a note 1 |  200 |             |        |
|      238 | Client A | A123  | Nick 123 | 2018-03-02 | This is a note 2 |  150 |             |        |
|      238 | Client A | A123  | Nick 123 | 2018-03-03 | This is a note 3 |  100 |             |        |
|      238 | Client A | A123  | Nick 123 | 2018-03-04 | This is a note 7 |  200 |             |        |
|      238 | Client A | A123  | Nick 123 | 2018-03-05 | This is a note 8 |  150 |             |        |
|      238 | Client A | A123  | Nick 123 | 2018-03-06 | This is a note 9 |  100 |             |        |
|      238 | Client A | A123  | Nick 123 |            |                  |      | 2018-03-01  |    100 |
|      238 | Client A | A123  | Nick 123 |            |                  |      | 2018-03-02  |    500 |
|      238 | Client A | A125  | Nick 125 | 2018-03-01 | This is a note 10|  200 |             |        |
|      238 | Client A | A125  | Nick 125 | 2018-03-03 | This is a note 13|  100 |             |        |
|      238 | Client A | A125  | Nick 125 |            |                  |      | 2018-03-01  |    300 |
|      238 | Client A | A125  | Nick 125 |            |                  |      | 2018-03-02  |    250 |
|      238 | Client A | A126  | Nick 126 | 2018-03-02 | This is a note 12|  150 |             |        |
|      238 | Client A | A126  | Nick 126 |            |                  |      | 2018-03-01  |    150 |
|      238 | Client A | A126  | Nick 126 |            |                  |      | 2018-03-02  |    500 |
+----------+----------+-------+----------+------------+------------------+------+-------------+--------+

 

  • Like 1

Share this post


Link to post
Share on other sites

Thank you. Sorry about the mix up - there is extra fields in the query but I had removed them in my editing.

I will try this and let you know.

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.