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

Using my Dolibarr
1

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:

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

 
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

2

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’

3

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

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)