We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

What is wrong with this mysql syntax?

JohnnyBCJ
JohnnyBCJ asked
on
Medium Priority
555 Views
Last Modified: 2012-05-11
When I run the following query, I get less than 10 records when I should be getting several hundred records. This code was originally made for MS SQL but got changed to MySQL.

Here is the query that I am trying to run:

SELECT Offices.OFFICE, Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND AS 'EXP_REF', Disbursements.C_PAY_OUT AS 'PAYOUT', Disbursements.CKAMT FROM Disbursements
INNER JOIN  Offices ON Disbursements.OFFICE = Offices.OFFICE
RIGHT OUTER JOIN  Clients ON Disbursements.SIN = Clients.SIN
LEFT OUTER JOIN  Receipts ON Clients.SIN = Receipts.SIN
AND Disbursements.YEAR = Receipts.YEAR
WHERE (Disbursements.SEQ like 'CB%')
AND (Disbursements.CKDATE <= '2011-3-31 23:59:59')
AND (Receipts.DATE_REC IS NULL)
OR (Disbursements.SEQ like 'CB%')
AND (Disbursements.CKDATE <= '2011-3-31 23:59:59')
AND (Receipts.DATE_REC <= '2011-3-31 23:59:59')
GROUP BY Offices.OFFICE, Clients.LNAME, Clients.FNAME,
Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND,  
Disbursements.CKAMT, Disbursements.C_PAY_OUT
HAVING (Disbursements.C_SCH1_REFUND > 0)
AND (SUM(CASE WHEN Receipts.Type = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) IS NULL)
ORDER BY Offices.OFFICE, Disbursements.CKDATE, Clients.LNAME

I get the same results if I modify the above query to the following:

SELECT Offices.OFFICE, Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND AS 'EXP_REF', Disbursements.C_PAY_OUT AS 'PAYOUT', Disbursements.CKAMT FROM Disbursements
INNER JOIN  Offices ON Disbursements.OFFICE = Offices.OFFICE
RIGHT OUTER JOIN  Clients ON Disbursements.SIN = Clients.SIN
LEFT OUTER JOIN  Receipts ON Clients.SIN = Receipts.SIN
AND Disbursements.YEAR = Receipts.YEAR
WHERE (Disbursements.SEQ like 'CB%')
AND (Disbursements.CKDATE <= '2011-3-31 23:59:59')
AND (Receipts.DATE_REC IS NULL)
GROUP BY Offices.OFFICE, Clients.LNAME, Clients.FNAME,
Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND,  
Disbursements.CKAMT, Disbursements.C_PAY_OUT
HAVING (Disbursements.C_SCH1_REFUND > 0)
ORDER BY Offices.OFFICE, Disbursements.CKDATE, Clients.LNAME


Any advice on what could be wrong with my query?

Comment
Watch Question

Rajkumar GsSoftware Engineer

Commented:
One thought - Since you using INNER JOIN, the result will contain only those record that are common in both tables.

Your thoughts ? Plz check this

Raj

Author

Commented:
Good Eye!

The inner join is OK. There are always going to be an office record associated with the disbursements.

Author

Commented:
Maybe this will shed some light on my problem.

The following query should be giving me a few hundred records but it doesn't give me any.
SELECT 
Offices.OFFICE, 
Clients.LNAME, 
Clients.FNAME, 
Clients.SIN, 
Disbursements.CKDATE, 
Disbursements.CKNUM, 
Disbursements.C_SCH1_REFUND AS 'EXP_REF', 
Disbursements.C_PAY_OUT AS 'PAYOUT', 
Disbursements.CKAMT FROM Disbursements 
INNER JOIN  Offices ON Disbursements.OFFICE = Offices.OFFICE 
RIGHT OUTER JOIN  Clients ON Disbursements.SIN = Clients.SIN 
LEFT OUTER JOIN  Receipts ON Clients.SIN = Receipts.SIN 
AND Disbursements.YEAR = Receipts.YEAR
WHERE (Disbursements.SEQ like 'CB%') 
AND (Disbursements.CKDATE <= '2011-3-31 23:59:59') 
AND (Receipts.DATE_REC <= '2011-3-31 23:59:59') 
GROUP BY Offices.OFFICE, Clients.LNAME, Clients.FNAME, 
Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND,   
Disbursements.CKAMT, Disbursements.C_PAY_OUT 
HAVING (Disbursements.C_SCH1_REFUND > 0) 
AND (SUM(CASE WHEN Receipts.Type = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) IS NULL) 
ORDER BY Offices.OFFICE, Disbursements.CKDATE, Clients.LNAME

Open in new window

Rajkumar GsSoftware Engineer

Commented:
Good - Similarly
RIGHT OUTER JOIN  Clients ON Disbursements.SIN = Clients.SIN
This RIGHT OUTER JOIN would return only those records that are in Clients table

LEFT OUTER JOIN  should fetch only those records that are in left side of that join
RIGHT OUTER JOIN should fetch only those records that are in right side of that join

Please check on this.
Raj
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
mixing left and right joins is usally calling for trouble.

not that if you have outer joins, and put conditions (for the outer joined tables) then into the where clause, it make the join a inner join implicitly.

if you want to start from clients (aka make sure you get all client records), start with

FROM CLIENTS
LEFT JOIN ...
LEFT JOIN ...
 
etc

Author

Commented:
Give me a minute and I'll attempt to do this with all left joins.
Great advice so far guys!

Author

Commented:
I guess I'm gonna learn a quick lesson on joins.

RajkumarGS:
Good - Similarly
RIGHT OUTER JOIN  Clients ON Disbursements.SIN = Clients.SIN
This RIGHT OUTER JOIN would return only those records that are in Clients table

LEFT OUTER JOIN  should fetch only those records that are in left side of that join
RIGHT OUTER JOIN should fetch only those records that are in right side of that join

Please check on this.
Raj


Here is a bit more information that might be helpful.
There is always a client record for every disbursement.
There is always an office record for every disbursement.
There is not always a receipt record for every disbursement.

I'm selecting the disbursements that meet one of the two following criteria:

1. Do not have a receipt associated with it.
2. If it has a receipt associated with it, the receipt.date_rec is after the date selected

I'm hoping I got the left join correct now. I still get zero records if I change the query to the following:


SELECT 
Offices.OFFICE, 
Clients.LNAME, 
Clients.FNAME, 
Clients.SIN, 
Disbursements.CKDATE, 
Disbursements.CKNUM, 
Disbursements.C_SCH1_REFUND AS 'EXP_REF', 
Disbursements.C_PAY_OUT AS 'PAYOUT', 
Disbursements.CKAMT FROM Disbursements 
INNER JOIN  Offices ON Disbursements.OFFICE = Offices.OFFICE 
LEFT OUTER JOIN  Clients ON Clients.SIN = Disbursements.SIN 
LEFT OUTER JOIN  Receipts ON Clients.SIN = Receipts.SIN 
AND Disbursements.YEAR = Receipts.YEAR
WHERE (Disbursements.SEQ like 'CB%') 
AND (Disbursements.CKDATE <= '2011-3-31 23:59:59') 
AND (Receipts.DATE_REC <= '2011-3-31 23:59:59') 
GROUP BY Offices.OFFICE, Clients.LNAME, Clients.FNAME, 
Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND,   
Disbursements.CKAMT, Disbursements.C_PAY_OUT 
HAVING (Disbursements.C_SCH1_REFUND > 0) 
AND (SUM(CASE WHEN Receipts.Type = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) IS NULL) 
ORDER BY Offices.OFFICE, Disbursements.CKDATE, Clients.LNAME

Open in new window

Rajkumar GsSoftware Engineer

Commented:
Are you sure the WHERE conditions satisfy the count of the records that you are expecting ?
Disbursements.SEQ like 'CB%'
Disbursements.CKDATE <= '2011-3-31 23:59:59'
Receipts.DATE_REC <= '2011-3-31 23:59:59'
HAVING (Disbursements.C_SCH1_REFUND > 0)
(SUM(CASE WHEN Receipts.Type = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) IS NULL)

Raj

Author

Commented:
Let me add to what I just said...

Here is a bit more information that might be helpful.

1. There is always a client record for every disbursement.
2. There is always an office record for every disbursement.
3. There is not always a receipt record for every disbursement.
4. It is possible to have 2 receipt records (with different date_rec dates) associated with 1 disbursement.

I'm selecting the disbursements that meet one of the two following criteria:

1. Do not have a receipt associated with it. (This is the results I am currently getting)

2. If the disbursement has a receipt associated with it, the receipt.date_rec is after the date selected
(These are the records I am not getting).


For example:

The date i selected is 2011-4-1.
If there is a disbursement that was done on 2011-31-1 and the receipt associated with this record has a date_rec of 2011-4-14, it should display but it isn't.
Rajkumar GsSoftware Engineer

Commented:
>> The date i selected is 2011-4-1.
If there is a disbursement that was done on 2011-31-1 and the receipt associated with this record has a date_rec of 2011-4-14, it should display but it isn't.

But your WHERE condition is
Receipts.DATE_REC <= '2011-3-31 23:59:59'
Then how the receipt record on '2011-4-14' should display ?

Raj

Author

Commented:
Disbursements.SEQ like 'CB%'
- This line is OK but we can remove it for arguments sake. This is one of the ways I can tell what office the disbursement came from.

Disbursements.CKDATE <= '2011-3-31 23:59:59'
- I only want to look at disbursements who date is less than or equal to '2011-3-31 23:59:59'

Receipts.DATE_REC <= '2011-3-31 23:59:59'
- IF there are any receipts associated with the disbursement, I only want to look at the receipts that have a dateless than or equal to '2011-3-31 23:59:59'

-If the receipt.DATE_REC is on 2011-4-1 00:00:00 or later, I don't want to look at the receipt.

HAVING
(Disbursements.C_SCH1_REFUND > 0)
- Disbursement must have a SCH1_Refund greater than 0

(SUM(CASE WHEN Receipts.Type = 'RV' THEN COALESCE (Receipts.AMT_REC, 0) ELSE NULL END) IS NULL)
- The only receipt types I'm interested in are where Type='RV'.
- If there is a receipt associated with the disbursement happened before the date selected (which is filtered above), and has an Amt_Rec on it, I do not want to display the disbursement.

Think of this as a way of balancing.
At the end of the year, every disbursement will have a receipt associated with it.
I want to be able to go back in time and say on as at '2011-3-31 23:59:59', there were X amount of disbursements that did not have a receipt associated with them. I want to display these disbursements.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
LEFT OUTER JOIN  should fetch only those records that are in left side of that join
RIGHT OUTER JOIN should fetch only those records that are in right side of that join


for a join like FROM table_a LEFT JOIN table b ... is that records from table_a will be returned, even if there is no matching record in table_b.
your explanation is badly worded, possibly misleading.

Author

Commented:
RajkumarGS:
>> The date i selected is 2011-4-1.
If there is a disbursement that was done on 2011-31-1 and the receipt associated with this record has a date_rec of 2011-4-14, it should display but it isn't.

But your WHERE condition is
Receipts.DATE_REC <= '2011-3-31 23:59:59'
Then how the receipt record on '2011-4-14' should display ?

Raj


If the disbursement was done on 2011-3-31 and the receipt was done on 2011-4-14
and I selected the date as:

>= 2011-3-31 and <= 2011-4-13 -> Disbursement should display

If I was to select a date 2011-4-14 or after, the disbursement should not display.

Author

Commented:
angelIII:
LEFT OUTER JOIN  should fetch only those records that are in left side of that join
RIGHT OUTER JOIN should fetch only those records that are in right side of that join

for a join like FROM table_a LEFT JOIN table b ... is that records from table_a will be returned, even if there is no matching record in table_b.
your explanation is badly worded, possibly misleading.


I assumed that is what he meant. I appreciate you clearing that up tho! As it removes any doubt :-)
Rajkumar GsSoftware Engineer

Commented:
AngelII,
I meant this - http://www.w3schools.com/sql/sql_join_left.asp
Sorry for my bad English
Raj

Author

Commented:
Hopefully this will clear things up a little bit more.

This is what I want to accomplish:

1. I want to select Disbursements having a CKDATE <= the date selected which is filtered by the 3 possible associations when it comes with Disbursements and Receipts.

Display Disbursements that meet the following::
1. There is no receipts record associated with the disbursement . (This is the results I am getting)
2. There is a receipt record that happened after the date selected. (This is not working as planned)

Do not display:
1. There is a receipt record that happened before or equal to the date selected. (This is working)

Author

Commented:
!!!!!!!!!!!!!!! LET ME REPEAT PLEASE!!!!!!!!!!!!

Hopefully this will clear things up a little bit more.

This is what I want to accomplish:

1. I want to select Disbursements having a CKDATE <= the date selected which is filtered by the 3 possible associations when it comes with Disbursements and Receipts.

Display Disbursements that meet the following::
1. There is no receipts record associated with the disbursement . (This is the results I am getting)
2. There is a receipt record that happened after the date selected. (This is not working as planned)

Do not display:
1. There is a receipt record that happened before or equal to the date selected. (THIS IS NOT WORKING)

What I am getting is:
- Disbursements that have no receipts associated with them at all.

 I selected a date of 2011-3-31.

- I will not get any disbursements that happened after 2011-3-31 (this is good).

- If a disbursement happened before 2011-3-31 and has a receipt dated after 2011-3-31, it should
display, but is not.

I am gone to lunch now, I'll be back within an hour. Please post any suggestions and I'll try them ASAP!

Author

Commented:
Does anyone have any other suggestions? I'm sure this problem is something very simple but hard to find!
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
My comment is the end solution. I asked if anyone felt like they deserved points and no one replied so I'm closing my question.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.