2. PHPRunner General questions
  3. How to query table a with no matches on table b ??
This topic is locked

How to query table a with no matches on table b ??

 		1/12/2011 8:30:47 PM
PHPRunner General questions
C
 cfernandez author

Hello,
5 days on this PHP Runner i don't know anything on PHP or SQL so this program has helped, but just can't figure this one out.
I got two tables, invoices and payments where each invoice has several payments. I need to query a list for invoices not paid in full.
So far i got:
table1: invoices(columns:id,date,payment_type,amount)
table2: payments(columns:id,invoice_id,amount)
linked invoices.id to payments.invoice_id as left outer join
query:
grouping by invoices.id and sum(payments.amount) - gives me the total paid on each invoice
temp column: invoices.amount-sum(payments.amount) - gives me the balance on each invoice
But since there are invoices with no payments at all then i cant figure out a way to filter this !!
Any ideas ? Please Help ! THANKS
Sergey Kornilov admin 1/13/2011

I would ask you to post your existing query and a sample results it returns. I'm pretty sure this something minor, just need to have a better picture.
C
 		cfernandez author 1/20/2011



I would ask you to post your existing query and a sample results it returns. I'm pretty sure this something minor, just need to have a better picture.


This is the query for the two tables:
SELECT

pagos.id,

pagos.fecha,

pagos.forma_de_pago,

pagos.cantidad,

pagos.movimiento,

pagos.referencia,

SUM(ventas.cantidad) AS pagado,

pagos.cantidad-sum(ventas.cantidad) AS balance

FROM pagos

LEFT OUTER JOIN ventas ON pagos.id = ventas.pagos_id

GROUP BY pagos.id

ORDER BY pagos.id DESC
Basically i get every row for table "pagos" for the ones with payments i get the total paid and the balance left.
What I really want is to get only those rows from "pagos" that still have a balance left.
Sergey Kornilov admin 1/20/2011

I need to see results of this query and bit of info on what doesn't look right.