# (Invoices) Need some help on Mysql Query, please!

**URL:** <https://www.dolibarr.org/forum/t/invoices-need-some-help-on-mysql-query-please/8722>\
**Category:** Using my Dolibarr\
**Created:** [September 13, 2012, 12:59pm UTC](https://www.dolibarr.org/forum/t/invoices-need-some-help-on-mysql-query-please/8722 "2012-09-13T12:59:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![alejandror](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/alejandror/32/172_2.png) [@alejandror](https://www.dolibarr.org/forum/u/alejandror)\
**Post date:** [September 13, 2012, 12:59pm UTC](https://www.dolibarr.org/forum/t/invoices-need-some-help-on-mysql-query-please/8722/1 "2012-09-13T12:59:16Z")

</div>

Hi all:  
I need a list of suppliers Invoices meeting following conditions:  
field: datef must be before 30/06/2012 and payment must be after that date  
I have been trying some queries i’ve read and googled like this:

```auto
SELECT a.* FROM llx_facture_fourn a
 JOIN llx_paiementfourn_facturefourn b
    ON a.b = b.id
 JOIN llx_paiementfourn c
    ON a.c = c.id
WHERE llx_paiementfourn.datep >2012-07-01
AND llx_facture_fourn.datef <2012-06-30
```

It gives me errors I’m not very skilled on writing sql queries  
I’ve tryed also this one

```auto
 
SELECT *
FROM llx_facture_fourn
WHERE rowid
IN (
SELECT fk_facturefourn
FROM llx_paiementfourn_facturefourn
INNER JOIN llx_paiementfourn ON fk_paiementfourn = llx_paiementfourn.rowid
INNER JOIN llx_facture_fourn ON fk_facturefourn = llx_facture_fourn.rowid
WHERE llx_paiementfourn.datep > '2012-07-01'
AND llx_facture_fourn.datef < '2012-06-30'
)
LIMIT 0 , 200
```

this one gives me empty result (that’s not correct)  
Any help or advice would be really appreciated  
Thanks in advance  
Ale

---

<div class="post-metadata">

**Author:** ![eldy](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/eldy/32/12_2.png) [@eldy](https://www.dolibarr.org/forum/u/eldy)\
**Post date:** [September 14, 2012, 7:27am UTC](https://www.dolibarr.org/forum/t/invoices-need-some-help-on-mysql-query-please/8722/2 "2012-09-14T07:27:32Z")

</div>

SELECT a.\* FROM llx\_facture\_fourn a  
JOIN llx\_paiementfourn\_facturefourn b  
ON a.rowid = b.fk\_facturefourn  
JOIN llx\_paiementfourn c  
ON b.fk\_paiementfourn = c.fk\_paiement  
WHERE c.datep \> '2012-07-01 ’  
AND a.datef \< ‘2012-06-30’

---

<div class="post-metadata">

**Author:** ![alejandror](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/alejandror/32/172_2.png) [@alejandror](https://www.dolibarr.org/forum/u/alejandror)\
**Post date:** [September 15, 2012, 12:58pm UTC](https://www.dolibarr.org/forum/t/invoices-need-some-help-on-mysql-query-please/8722/3 "2012-09-15T12:58:46Z")

</div>

Many Thanks Eldy!  
Problems seems to be in Myadmin something has changed and myadmin doesn’t accepts apostrophes for date numbersand doest either recognizes the dates as dates without apostrophes.  
it’s a problem… I always use Myadmin for database operations

So i tested MysqlWorkbench and your query suggestion doesn’t gives me the expected result

```auto
SELECT a.* FROM llx_facture_fourn a
JOIN llx_paiementfourn_facturefourn b
ON a.rowid = b.fk_facturefourn
JOIN llx_paiementfourn c
ON b.fk_paiementfourn = c.fk_paiement
WHERE c.datep > '2012-07-01'
AND a.datef < '2012-06-30'
```

I tryed again my “cromagnon style” query :

`
SELECT * FROM llx_facture_fourn WHERE rowid
IN (
SELECT fk_facturefourn
FROM llx_paiementfourn_facturefourn
JOIN llx_paiementfourn ON fk_paiementfourn = llx_paiementfourn.rowid
JOIN llx_facture_fourn ON fk_facturefourn = llx_facture_fourn.rowid
WHERE llx_paiementfourn.datep > 2012-06-30
AND llx_facture_fourn.datef < 2012-06-30
)
LIMIT 0 , 200`

and now it shows me what I need…  
Now my problem is that I really preffer your way because it looks much more logic an correct use of JOIN ON than my rustic Use of subqueries

what could be the problem?  
Once again I thank you for your patience and help.

PS  
I was thinking that this query could be useful as a dolibarr new feature, is very comon the need of finding invoices betwen dates with payments after or before certain dates (anual balance, or project dues)