Question 20574 in Mysql Question posted in
The official MySQL documentation can be found here.

Pull out data from invoice and fees table based on ranges mysql

I have 3 tables members fees and tblinvoice All they have a memberid
So if someone pays data goes to fees and tblinvoice and invoiceid is created in a fees table and tblinvoice plus memberid is inserted in these two tables two know which member paid.
now when the invoice is created only the tblinvoice recieves data but still with the memberid.

Now I want to pull out data from the 3 tables but the two tables fees and tblinvoice data will be based on the date range from tblinvoice which is invoicedate and also from tblinvoice date from fees fees.paiddate.

Now if there was data in table fees put it and if there is nothing pull out from tblinvoice or if both pull out from both.

The objective here is to pull out a statement for payments and invoiced amount. I wonder if I explained well this is what I have tried below am using mysql.

select 
members.memberid,
fees.feesid,
members.usercode,
members.title, 
members.name,
members.surname,
members.address, 
members.city, 
members.cell, 
fees.recieved_by, 
fees.paidfor,
fees.fromdate,
fees.amountpaid AS amountpaidMe, 
fees.feestype,
fees.qty,
fees.paiddate,
fees.unitPrice,
fees.todate,
fees.invoiceid,
tblinvoice.amount AS invoicedAmount,
tblinvoice.invoicedate

FROM members

INNER JOIN  fees ON fees.memberid = members.memberid
LEFT OUTER JOIN  tblinvoice  ON tblinvoice.memberid = fees.memberid

WHERE members.memberid ='62396'
&& tblinvoice.invoicedate BETWEEN '2023-01-01' AND  '2023-03-05' OR  fees.paiddate BETWEEN '2023-01-01' AND  '2023-03-05'

I was asked to share all the data in the three tables and below is the data.
Bu if I trie to query using the date ranges it get everytihng wrong
I want to get all data from the fees tabale base on paiddate range and all data from invoice table based on invoicedate range is one table has no data just make that line black then pull out the data in another table

Query Members 
SELECT name from members where memberid = '62396' 
Results 
Name 
Abdullah Bin Salaam


Query fees table 
SELECT `amountpaid` , `paiddate` , `memberid` FROM `fees` WHERE memberid = '62396'

results 



    Full texts                                         amountpaid      paiddate       memberid
    Edit Edit   Copy Copy   Delete Delete   1300.00     2022-08-18 11:44:44     62396
    Edit Edit   Copy Copy   Delete Delete   1300.00     2022-09-13 03:56:50     62396
    Edit Edit   Copy Copy   Delete Delete   650.00      2022-11-15 04:12:34     62396
    Edit Edit   Copy Copy   Delete Delete   1500.00     2022-12-07 09:37:13     62396
    Edit Edit   Copy Copy   Delete Delete   1300.00     2023-01-13 06:00:58     62396
    Edit Edit   Copy Copy   Delete Delete   1300.00     2023-01-24 05:55:11     62396
    Edit Edit   Copy Copy   Delete Delete   1300.00     2023-02-03 08:36:36     62396
    Edit Edit   Copy Copy   Delete Delete   1300.00     2023-02-21 08:38:53     62396



Query tbl invoice table 
SELECT `amount`, `memberid`, `invoicedate` FROM `tblinvoice` WHERE memberid = '62396' 

results 


Full texts                                         amount   memberid invoicedate
    Edit Edit   Copy Copy   Delete Delete   340.00  62396   2023-03-01 18:27:18
    Edit Edit   Copy Copy   Delete Delete   89.00   62396   2023-03-01 18:28:41
    Edit Edit   Copy Copy   Delete Delete   88.00   62396   2023-03-01 18:28:41
    Edit Edit   Copy Copy   Delete Delete   21.00   62396   2023-03-02 09:22:01
    Edit Edit   Copy Copy   Delete Delete   55.00   62396   2023-03-02 09:22:01
    Edit Edit   Copy Copy   Delete Delete   890.00  62396   2023-03-02 11:51:27
Tags:

2

Answers


  1. 0

    Instead of trying to limit the rows from Fees or Invoices in the final where clause, try including the date ranges into the join conditions. e.g.

    SELECT
      members.memberid
    , fees.feesid
    , members.usercode
    , members.title
    , members.name
    , members.surname
    , members.address
    , members.city
    , members.cell
    , fees.recieved_by
    , fees.paidfor
    , fees.fromdate
    , fees.amountpaid AS amountpaidMe
    , fees.feestype
    , fees.qty
    , fees.paiddate
    , fees.unitPrice
    , fees.todate
    , fees.invoiceid
    , tblinvoice.amount AS invoicedAmount
    , tblinvoice.invoicedate
FROM members
INNER JOIN fees ON fees.memberid = members.memberid
    AND fees.paiddate BETWEEN '2023-01-01' AND '2023-03-05'
LEFT OUTER JOIN tblinvoice ON tblinvoice.memberid = fees.memberid
    AND fees.invoiceid = tblinvoice.id
    AND tblinvoice.invoicedate BETWEEN '2023-01-01' AND '2023-03-05'
WHERE members.memberid = '62396'

    Note, if you have a left joined table, then use columns from that table in the where clause you can cause the effect of an inner join unless NULLs due to unmatched rows are explicitly permitted. By moving the date range filters into the joins you avoid this added complexity.

    Reverse engineered DDL & sample data with operating query (as seen above) can be found here: https://dbfiddle.uk/35z4cps8

    Login or Signup to reply.
  2. 0

    You may have more success by trying a union of invoices and fees: e.g.

    SELECT
     members.memberid, invoicedate as transdate, 'invoice' as type, amount as invoiced, NULL as paid
FROM members
INNER JOIN invoice ON invoice.memberid = members.memberid
WHERE members.memberid = '62396'
AND invoice.invoicedate BETWEEN '2023-01-01' AND '2023-03-05'

UNION ALL

SELECT
     members.memberid, paiddate as transdate, 'fee' as type, NULL, - amountpaid as amount
FROM members
INNER JOIN fees ON fees.memberid = members.memberid
WHERE members.memberid = '62396'
AND fees.paiddate BETWEEN '2023-01-01' AND '2023-03-05'
ORDER BY
     memberid, transdate

    Result

    memberid    transdate           type    invoiced    paid
62396   2023-01-13T06:00:58Z    fee (null)  -1300
62396   2023-01-24T05:55:11Z    fee (null)  -1300
62396   2023-02-03T08:36:36Z    fee (null)  -1300
62396   2023-02-21T08:38:53Z    fee (null)  -1300
62396   2023-03-01T18:27:18Z    invoice 340 (null)
62396   2023-03-01T18:28:41Z    invoice 89  (null)
62396   2023-03-01T18:28:41Z    invoice 88  (null)
62396   2023-03-02T09:22:01Z    invoice 21  (null)
62396   2023-03-02T09:22:01Z    invoice 55  (null)
62396   2023-03-02T11:51:27Z    invoice 890 (null)

    DDL & DML

    CREATE TABLE Members (
    memberid INT,
    Name VARCHAR(255)
);

CREATE TABLE fees (
    amountpaid FLOAT,
    paiddate DATETIME,
    memberid INT
);
CREATE TABLE invoice (
    amount FLOAT,
    memberid INT,
    invoicedate DATETIME
);

INSERT INTO Members (memberid,Name)
VALUES (62396,'Abdullah Bin Salaam');

INSERT INTO fees (amountpaid, paiddate, memberid)
VALUES
(1300.00,'2022-08-18 11:44:44',62396),
(1300.00,'2022-09-13 03:56:50',62396),
(650.00,'2022-11-15 04:12:34',62396),
(1500.00,'2022-12-07 09:37:13',62396),
(1300.00,'2023-01-13 06:00:58',62396),
(1300.00,'2023-01-24 05:55:11',62396),
(1300.00,'2023-02-03 08:36:36',62396),
(1300.00,'2023-02-21 08:38:53',62396);

INSERT INTO invoice (amount, memberid, invoicedate)
VALUES
(340.00,62396,'2023-03-01 18:27:18'),
(89.00,62396,'2023-03-01 18:28:41'),
(88.00,62396,'2023-03-01 18:28:41'),
(21.00,62396,'2023-03-02 09:22:01'),
(55.00,62396,'2023-03-02 09:22:01'),
(890.00,62396,'2023-03-02 11:51:27');

    see: http://sqlfiddle.com/#!9/6769ff/5

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search