Jump to content

Report table not producing separate rows


gabucknall

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?

Link to comment
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?

Link to comment
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.

Link to comment
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

 

Link to comment
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

Link to comment
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 |
+----------+----------+-------+----------+------------+------------------+------+-------------+--------+

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.