Question: I have two tables with a 1:M relationship: Account (Parent) and 'Payment' (Child). The 'Payment' table has a column 'Type' whose value could be 'Paid', 'will-Pay' or 'Not-paid'. 

Now I need to write a query which can fetch all Account tables record and it's child records. But in the child table it should first check if there is any 'Paid' type record present for it Parent Account Table. If yes, then it will pick that only (Not other types). If 'Paid' type record is not present in child table, then it will check for 'will-Pay' type child records and fetch them only.

Answer: 
Assuming you have at least:

```
CREATE TABLE account (
    id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE payment (
    account_id INTEGER NOT NULL REFERENCES account(id),
    type ENUM ('Paid', 'will-Pay', 'Not-paid')
);

```

'Paid' records and 'will-Pay' records in the same answer:

```
SELECT
    account.*, payment.*
FROM
    account
    INNER JOIN payment
        ON (payment.account_id = account.id)
WHERE payment.type IN ('Paid', 'will-Pay');

```

'Paid' records and 'will-Pay' records in the separate answers:

```
-- Fetch 'Paid' records
SELECT
    account.*, payment.*
FROM
    account
    INNER JOIN payment
        ON (payment.account_id = account.id)
WHERE payment.type = 'Paid';

-- Fetch 'will-Pay' records
SELECT
    account.*, payment.*
FROM
    account
    INNER JOIN payment
        ON (payment.account_id = account.id)
WHERE payment.type = 'will-Pay';

```
Try:

```
SELECT p.*
FROM account a
INNER JOIN payment p ON p.account_id = a.id
WHERE p.type = 'Paid' or 
      (p.type = 'will-Pay' and not exists
       (select null from payment p1 where p1.account_id = a.id and p1.type = 'Paid')
      )

```