How to retrieve rows from table 1 that have no related rows in table 2 in MySQL

I have an interesting question - I need the exact reverse of a JOIN statement.

I have a table called invoices, and another table called payments. I want to retrieve, in order, invoices that have NO payments attached to it whatsoever, but while followed by rows that have payments attached to it after that.

invoices table has these fields:

• id
• customerid
• timestamp

payments table has these fields:

• id
• invoiceid
• amount

Not all payments are full amounts, so each invoice could have multiple payments attached.

I want the unpaid invoi开发者_JAVA技巧ces to come up first (no payments) and then followed by invoices with partial payments but not full, and then followed by fully paid invoices.

I wish to perform this without having to run separate SQL queries if I can - I'd rather it be one query so I can spit out the once. Any ideas?

-- UPDATED --

Based on an answer from DCP, I've updated the query method. I had it working until I needed to add that JOIN statement to also order by my sub_to_inv table which looks like this:

id subscription invoice 0 12 18 1 13 18 2 14 19

etc.

So based on this I came up with this (added the LIMIT at the end ot limit by pages..)

SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, 0 paidratio FROM invoices i
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE NOT EXISTS (SELECT id FROM payments p WHERE p.invoice=i.id) AND i.corporation='3' AND i.payer=1

UNION ALL
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paidratio FROM invoices i,
(SELECT p.invoice, sum(amount) AS paid FROM payments p GROUP BY p.invoice) p
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE p.invoice=i.id AND i.corporation='3' AND i.payer=1

AND p.paid UNION ALL
SELECT i.id AS id, i.modify_date as modify_date, s.subscription as subscriptionid, p.paid/i.total AS paitratio FROM invoices i,
(SELECT p.invoice, sum(amount) AS paid FROM payments p WHERE p.invoice=id GROUP BY p.invoice) p
LEFT JOIN sub_to_inv s ON i.id=s.invoice
WHERE p.invoice = i.id AND i.corporation='3' AND i.payer=1

AND p.paid=i.total

ORDER BY paidratio DESC, modify_date DESC, subscriptionid ASC LIMIT 0,40

I get a "i.id column not found" error which I don't get. On each block of statements that are ultimately combined through teh UNION statement, I specify invoices i to ensure that i.id refers to the id of the invoices. tab.e

You didn't tell us how to determine the total invoice amount, but I assume you have some field on your invoice table for this. In the example below, I assumed it's called TOT_AMT.

The idea behind this approach is that we get the data in three separate steps, and we use a UNION ALL to glue the three pieces together. We use a pseudocolumn called PAY_TYPE to get the ordering you want, which is invoices with no payments first, then invoices with partial payments, and finally, invoices which are fully paid.

A complete working example is shown below (I used Oracle, but I think you can adapt it to MySQL easily).

CREATE TABLE invoices (ID number, tot_amt NUMBER);
CREATE TABLE payments (ID NUMBER, invoice_id NUMBER, amount NUMBER);

INSERT INTO invoices VALUES (1,100);
INSERT INTO invoices VALUES (2,200);
INSERT INTO invoices VALUES (3,300);

INSERT INTO payments VALUES (1,1,20);
INSERT INTO payments VALUES (2,1,40);

INSERT INTO payments VALUES (3,3,150);
INSERT INTO payments VALUES (4,3,120);
INSERT INTO payments VALUES (5,3,30);

/* get invoices with no payments */
SELECT i.id
     , i.TOT_AMT
     , 0               TOT_PAID
     , '0_NO_PAYMENTS' PAY_TYPE
  FROM invoices i
  WHERE NOT EXISTS (SELECT * FROM PAYMENTS P WHERE P.INVOICE_ID = I.ID)
UNION ALL
/* get invoices with partial payments */
SELECT i.id
     , i.TOT_AMT
     , p.tot_paid
     , '1_PARTIALLY_PAID' PAY_TYPE
  FROM invoices i
     , (SELECT P.INVOICE_ID
             , SUM(AMOUNT) tot_paid
          FROM PAYMENTS P
         GROUP BY P.INVOICE_ID) p
 WHERE P.INVOICE_ID = I.ID
   AND p.TOT_PAID < i.TOT_AMT
UNION ALL
/* get invoices that are fully paid */
SELECT i.id
     , i.TOT_AMT
     , p.tot_paid
     , '2_FULLY_PAID' PAY_TYPE
  FROM invoices i
     , (SELECT P.INVOICE_ID
             , SUM(AMOUNT) tot_paid
          FROM PAYMENTS P
         GROUP BY P.INVOICE_ID) p
 WHERE P.INVOICE_ID = I.ID
   AND p.TOT_PAID = i.TOT_AMT
ORDER BY PAY_TYPE   

Results:

    ID      TOT_AMT   TOT_PAID   PAY_TYPE
    2       200       0          0_NO_PAYMENTS
    1       100       60         1_PARTIALLY_PAID
    3       300       300        2_FULLY_PAID

If I'd assume that you have an invoice_details table which contains the total amount:

SELECT      i.id as invoiceid,
            case 
                when (select sum(amount) from payments where invoiceid = i.id group by invoiceid) = d.amount
                    then 3
                when (select sum(amount) from payments where invoiceid = i.id group by invoiceid) < d.amount
                    then 2
                else 1
            end 
            as paidstatus

FROM        invoices i
INNER JOIN  invoice_details d
ON          i.id = d.invoiceid
LEFT JOIN   payments p
ON          i.id = p.invoiceid
GROUP BY    i.id, d.amount
ORDER BY    paidstatus

Where paidstatus:

1 = unpaid
2 = partially paid
3 = paid

select * from table1 where not exists (select * from table2 where table2.foreignkey = table1.primarykey)

Does that work?

From your question, I don't see any way to tell if an invoice is fully paid, but the following will get you a list of invoices with no payments followed by a list of invoices with payments.

select *
from
 invoices
where
 not exists (
  select 1
  from payments
  where invoices.id = payments.invoiceid
 )

union

select *
from
 invoices
where
 exists (
  select 1
  from payments
  where invoices.id = payments.invoiceid
 )

SELECT i.id FROM invoices i
  LEFT JOIN PAYMENTS p ON i.id = p.invoice_id
  GROUP BY i.id, i.amount
  ORDER BY SUM(p.amount) / i.amount

If this works I'll be amazed.

select sub.*, paid/tot_amt as ordering_column
from (
 select
    i.id,
    i.customerid,
    i.timestamp,
    i.tot_amt,
    (select sum(ifnull(p.amount,0)) from payments p where p.invoiceid = i.id ) as paid
 from invoices i
) sub
order by ordering_column

this will give you the invoices sorted in way you wanted, and also a sum of the paid amount per invoice