I have been on this for over an hour and im getting frustrated. I have two tables, and Im doing a query where one of the tables is used only to check against a single condition, but currently I am getting duplicate results for ever entry found in the second table.
Tables: Customers & Invoices
First Query - Gets everyone:
Select cust_id
FROM Customers
WHERE clinic_id='$clinic'
This is working fine, just a simple query... My next goal is to join the second table, and show results ONLY where an invoice is found with-in the last 18 months.
Query:
Select Customers.cust_id
FROM Customers
LEFT JOIN Invoices
ON Customers.cust_id=Invoices.cust_id
WHERE Customers.clinic_id='$clinic'
AND Invoices.invoice_date > '$cutoffdate'
This gives me a result for EACH invoice found with-in the last 18 months, so if a person has 5 invoices, that person gets returned 5 times.
I need the query to return each person only once, and ONLY when it finds at least 1 invoice with-in the past 18 months.
How do I modify my query to do this?
I want to show two ways to get the result you describe.
First of all, if you want to show only customers who had invoices, you probably want to switch to an INNER JOIN instead of the LEFT JOIN. This would leave all the customers who didn't have any invoices out of your result, and as I understand this is what you want to archive. More about the difference of JOINs can be read about here.
1. DISTINCT
Just add DISTINCT after SELECT, it specifies removal of duplicate rows from the result set.
SELECT DISTINCT C.cust_id
FROM Customers C
INNER JOIN Invoices I ON C.cust_id = I.cust_id
WHERE C.clinic_id='$clinic'
AND I.invoice_date > '$cutoffdate'
2. GROUP BY
You can use GROUP BY to archieve the same effect, this is usually used when you aggregate something in there, p.e. count the bills or sum them up.
SELECT C.cust_id
FROM Customers C
INNER JOIN Invoices I ON C.cust_id = I.cust_id
WHERE C.clinic_id='$clinic'
AND I.invoice_date > '$cutoffdate'
GROUP BY C.cust_id
The most efficient method is to use exists:
Select c.cust_id
FROM Customers c
WHERE EXISTS (SELECT 1
FROM Invoices i
WHERE c.cust_id = i.cust_id AND
c.clinic_id = '$clinic' AND
i.invoice_date > '$cutoffdate'
);