• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

What is wrong with this mysql syntax?

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?

0
JohnnyBCJ
Asked:
JohnnyBCJ
  • 13
  • 5
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
JohnnyBCJAuthor Commented:
Good Eye!

The inner join is OK. There are always going to be an office record associated with the disbursements.
0
 
JohnnyBCJAuthor 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

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Rajkumar GsSoftware EngineerCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
JohnnyBCJAuthor Commented:
Give me a minute and I'll attempt to do this with all left joins.
Great advice so far guys!
0
 
JohnnyBCJAuthor 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

0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
JohnnyBCJAuthor 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.
0
 
Rajkumar GsSoftware EngineerCommented:
>> 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
0
 
JohnnyBCJAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
JohnnyBCJAuthor 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.
0
 
JohnnyBCJAuthor 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 :-)
0
 
Rajkumar GsSoftware EngineerCommented:
AngelII,
I meant this - http://www.w3schools.com/sql/sql_join_left.asp
Sorry for my bad English
Raj
0
 
JohnnyBCJAuthor 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)

0
 
JohnnyBCJAuthor 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!
0
 
JohnnyBCJAuthor Commented:
Does anyone have any other suggestions? I'm sure this problem is something very simple but hard to find!
0
 
JohnnyBCJAuthor Commented:
I solved this problem by changing the query completely.

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 'B%')
and (Disbursements.CKDATE <= '2011-5-24 23:59:59')
 and (Disbursements.C_SCH1_REFUND > 0)
AND (Receipts.DATE_REC IS NULL)
OR (Disbursements.SEQ like 'B%')
AND (Disbursements.CKDATE <= '2011-5-24 23:59:59')
AND (Disbursements.C_SCH1_REFUND > 0)
AND (Receipts.DATE_REC > '2011-5-24 23:59:59')
and Receipts.Type='RV'

GROUP BY Offices.OFFICE, Clients.LNAME, Clients.FNAME, Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM, Disbursements.C_SCH1_REFUND,   Disbursements.CKAMT, Disbursements.C_PAY_OUT ORDER BY Offices.OFFICE, Disbursements.CKDATE, Clients.LNAME

This gives me the information I was looking for.
If anyone feels like they deserve points, please let me know. If not, I'll request to close the question.
Thanks for your help!
0
 
JohnnyBCJAuthor 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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 13
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now