Menu
I have a query pulling data from three tables using LEFT OUTER JOIN for both joins. I need the query to return the left most (Salesrep table) info even if the there is no corresponding data in the two right tables (prescriber and prescriptions, respectively). When I run this query without the date parameters in the WHERE clause, I get the expected return, but as soon as I include the date parameters I get nothing returned where there is no matching data for a salesrep. I need to at least see the salesrep table columns requested in the query.
Here is the query... any help is VERY much appreciated.
SELECT salesrep.salesrepid as SalesRepID, salesrep.fname as SalesrepFName, salesrep.lname as SalesRepLName, salesrep.fname+' '+salesrep.lname as SalesRepFullName, prescriber.dea_no as PDeaNo, prescriber.lname+', '+prescriber.fname as DocName, CONVERT(VARCHAR(8), prescriptions.filldate, 1) as FillDate, prescriptions.drugname as DrugName, prescriptions.daysupply as Supply, prescriptions.qtydisp as QtyDisp, prescriptions.rx_no as Refill, prescriptions.copay as Sample, ROUND(prescriptions.AgreedToPay-(prescriptions.AgreedToPay*.07),2) as AgreedToPay, prescriptions.carrierid as CarrierID FROM salesrep LEFT OUTER JOIN prescriber on salesrep.salesrepid = prescriber.salesrepid LEFT OUTER JOIN prescriptions on prescriber.dea_no = prescriptions.dea_no WHERE salesrep.salesrepid = 143 AND prescriptions.filldate >= '09-01-12' AND prescriptions.filldate <= '09-17-12' ORDER BY prescriptions.filldate 
759
MySQL doesn't have syntax keyword FULL OUTER JOIN. You have to use combination of LEFT and RIGHT JOIN to obtain full joins. Show activity on this post. You're getting that error because MySQL does not support (or recognize) the FULL OUTER JOIN syntax.
There really is no difference between a LEFT JOIN and a LEFT OUTER JOIN. Both versions of the syntax will produce the exact same result in PL/SQL. Some people do recommend including outer in a LEFT JOIN clause so it's clear that you're creating an outer join, but that's entirely optional.
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
A cross join is a type of join that returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.
You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:
LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no AND prescriptions.filldate >= '09-01-12' AND prescriptions.filldate <= '09-17-12' Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
