Report table not producing separate rows

[gabucknall]

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 June 7, 2018 by gabucknall

[Barand]

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?

[gabucknall]

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.

[Barand]

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

 

[gabucknall]

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 June 7, 2018 by gabucknall

[Barand]

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 |
+----------+----------+-------+----------+------------+------------------+------+-------------+--------+

 

[gabucknall]

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.