CODEWITHSUNDEEP
mysql
Keith Power
Keith Power

Reputation: 14141

mySQL Query get record that dont match

I have an sql query that I wish to add another condition to but I cant seem to get it work. The query is simple enough:

SELECT DISTINCT monthly_returns.company_id
       FROM monthly_returns, paidreturns
       WHERE monthly_returns.company_id = paidreturns.company_id
       AND paidreturns.month =  '$cdate'
       AND paidreturns.paid =0

However I wish to get the records also from the monthly_returns that have not record at all in paidreturns for the give date. I know it would be something like this

SELECT  *
FROM    monthly_returns
WHERE   monthly_returns NOT IN (SELECT * FROM paidreturns WHERE paidreturns.month =  '$cdate')

paidreturns.paid =0 is where the bill has not been paid, but equally if there is no record for that date in paidreturns then the bill is also not paid.

The schema

paidreturns table

-id

-company_id

-paid

-month

-total

monthly_returns table

-id

-company_id

-wage_totals

-month

Upvotes: 0

Views: 62

Answers (2)

Thomas Kelley
Thomas Kelley

Reputation: 10302

Try this:

SELECT
    DISTINCT monthly_returns.company_id
FROM
    monthly_returns
LEFT JOIN
    paidreturns
    ON monthly_returns.company_id = paidreturns.company_id
       AND monthly_returns.month = paidreturns.month
WHERE 
    monthly_returns.month = '$cdate'
    AND
        (
        paidreturns.paid = 0
        OR
        paidreturns.company_id IS NULL
        );

Using a LEFT JOIN, you can find all records from monthly_returns, regardless of whether they matched an entry from paidreturns.

Then, by adding paidreturns.company_id IS NULL to the WHERE clause, you include those unmatched entries in your query.

Upvotes: 1

Val
Val

Reputation: 413

select company_id
from
(
(

SELECT DISTINCT monthly_returns.company_id
   FROM monthly_returns, paidreturns
   WHERE monthly_returns.company_id = paidreturns.company_id
   AND paidreturns.month =  '$cdate'
   AND paidreturns.paid =0
)
union
(


SELECT  company_id
FROM    monthly_returns
WHERE   monthly_returns NOT IN (SELECT * FROM paidreturns WHERE paidreturns.month = '$cdate'
)
) as x

Upvotes: 0

Related Questions