CODEWITHSUNDEEP
phpmysqljoin
Lan
Lan

Reputation: 1892

mysql query to find overlap in joined tables

I have 2 tables; profiles & invoices. profiles is the main table which has all my people in it. then some of these people have invoices (individuals can have none, one or more than one invoice). the invoice table has a field 'profileid' which links it to the relevant id in profiles. the invoice table has a field named 'paid' which says either 'Paid' or 'Unpaid'.

hope thats clear

now I have a search page where one can search all the profiles. I have 2 checkboxes so you can filter all those that have an invoices paid and all those that have unpaid invoices.

it works fine individually but not together. ie, if you tick box for invoices paid it gives you all profiles who have ever paid an invoice, and if you tick unpaid it gives you all people with unpaid invoices; but if you tick both boxes it gives you 0 results, when it should give you all the people who both have unpaid and paid invoices.

here is my count query:

$query = "SELECT COUNT(DISTINCT profiles.id) AS num FROM profiles 
LEFT JOIN invoices ON (profiles.id=invoices.profileid) 
AND (invoices.paid='Paid' OR invoices.paid='Unpaid')
WHERE
(
IFNULL(invoices.paid, '') LIKE '$paid%' 
AND IFNULL(invoices.paid, '') LIKE '$unpaid%' 
);

spent ages on this and can't get it to work, any help is much appreciated. thanks

Upvotes: 2

Views: 138

Answers (2)

Havenard
Havenard

Reputation: 27904

Its checking if its paid AND unpaid at the same time. Obviously it can never be both!

You will have to use OR or just skip this check in case both options are selected.

Upvotes: 1

mzedeler
mzedeler

Reputation: 4369

SELECT COUNT(DISTINCT profiles.id) AS num FROM profiles 
LEFT JOIN invoices ON (profiles.id=invoices.profileid) 
AND (invoices.paid='Paid' OR invoices.paid='Unpaid')
WHERE
IFNULL(invoices.paid, '') LIKE '$paid%' 
OR IFNULL(invoices.paid, '') LIKE '$unpaid%'

But a whole different thing is that you shouldn't use interpolated variables (like $paid above) and I find it odd that you have to use LIKE '[...]%' to select the right value. It looks as if the database schema is in a bad shape.

Upvotes: 0

Related Questions