CODEWITHSUNDEEP
phpmysql
Gihan Lasita
Gihan Lasita

Reputation: 3055

How to write a sql query to get this output?

I have database table with the follwoing fields:

id, loan_no, installment, status

which has loan information like

if a loan has 24 installments there will be 24 rows which contains same loan_no and installment status which is paid or unpiad.

So I want to get the loan_no's which has all installments status = paid.

Is it possible to write a one query to get result?

Upvotes: 1

Views: 205

Answers (6)

Vishal Suthar
Vishal Suthar

Reputation: 17194

SELECT loan_no FROM YOUR_TABLE
WHERE id NOT IN (SELECT id FROM YOUR_TABLE WHERE status = 'unpaid') GROUP BY loan_no

Upvotes: 0

CM Kanode
CM Kanode

Reputation: 1436

SELECT A.loan_no FROM (
    SELECT loan_no, COUNT(status) AS installments
    WHERE status = 'paid' 
    GROUP BY loan_no, status
) AS A
WHERE A.installments = 24

So all loans will always have 24 payments? And if you want to pull loan_no for all loans that have had all 24 payments made, then something like the above will work.

Upvotes: 0

aNi
aNi

Reputation: 1359

SELECT loan_no FROM table where status = "paid" group by loan_no;

Upvotes: 0

sorpigal
sorpigal

Reputation: 26086

Something like

select distinct
        i.loan_no,
        i.status
    from
        info as i
        left outer join info as i2
            on i.loan_no = i2.loan_no and i2.status = 'unpaid'
    where
        i.status = 'paid'
        and
        i2.loan_no is null
;

Upvotes: 1

Marco
Marco

Reputation: 57573

Try this:

SELECT loan_no FROM your_table
WHERE loan_no NOT IN
  SELECT DISTINCT loan_no FROM your_table
  WHERE status = 'unpaid')
GROUP BY loan_no

Upvotes: 1

ruakh
ruakh

Reputation: 183301

SELECT DISTINCT loan_no FROM loan
WHERE loan_no NOT IN (SELECT DISTINCT loan_no FROM loan WHERE status = 'unpaid')

Upvotes: 0

Related Questions