SQL Issue joining three tables

Hi. I am using the following SQL statement to get the total payments by CustID

SELECT a.[CustID] As [CUSTOMERS|CustID], Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] FROM [CUSTOMERS] a LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID]) WHERE b.[DATE]='25 September 2014' GROUP BY a.[CustID]

I have a second SQL statement
Select Distinct CustomerID, School  From CHILDREN

I want to combine the two queries to show all of the values in the first query and then an additional column called [School]

The column CustomerID in CHILDREN is the link to the CustID Column in CUSTOMERS
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this

SELECT a.[CustID] As [CUSTOMERS|CustID], c.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] 
FROM [CUSTOMERS] a 
LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID]) 
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
WHERE b.[DATE]='25 September 2014' 
GROUP BY a.[CustID], c.School

Open in new window

0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
Solution provided in above by Mr. Phillip Burton is just perfect. Above code is perfectly written in Microsoft SQL Query Syntax while following syntax will be used to solve the same requirement in Oracle Database

SELECT a.CustId, Sum(b.AMOUNT), c.School
from Customers a, Payments b, Children c
where a.CustId = b.LinkID (+)
and a.CustId = c.CustomerId (+)
and b.Date = '25-SEP-2014'
GROUP BY a.CustId, c.School
0
PortletPaulfreelancerCommented:
No points please:

ANSI 92 codified join syntax (e.g. LEFT JOIN) 22 years ago

Oracle has for many years supported ANSI 92 join syntax; It simply is not required by Oracle these days to use (+) and I would never recommend continuing to use it.

If in fact Oracle syntax was relevant to this question (which is does not appear to be) then the date filter would use to_date()
e.g. this would work in Oracle:
SELECT a.CustID As CUSTOMERS_CustID, c.School as School, Sum(b.AMOUNT) As PAYMENTS_AMOUNT
FROM CUSTOMERS a
LEFT JOIN PAYMENTS b ON a.CustID = b.LINK ID
LEFT JOIN CHILDREN c ON a.CustID = c.CustomerID
WHERE b.DATE=to_date('25-09-2014','DD-MM-YYYY')
GROUP BY a.CustID, c.School

I would however draw attention to the date filter used for SQL Sever. The safest date literal to use is YYYYMMDD and NOT month by its full name which is dependent on language settings.

Hence; for SQL Server I would recommend the following for the date filter:

WHERE b.[DATE]='20140925'

See (for SQL Server):
The ultimate guide to the datetime datatypes
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I can't seem work this out.
If I use

SELECT a.[CustID] As [CUSTOMERS|CustID], c.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT]
FROM [CUSTOMERS] a
LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID])
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
WHERE b.[DATE]='25 September 2014'
GROUP BY a.[CustID], c.School

I get

1
and if I run

SELECT Distinct a.[CustID] As [CUSTOMERS|CustID], c.School as School, b.[AMOUNT] As [PAYMENTS|AMOUNT]
FROM [CUSTOMERS] a
LEFT JOIN [PAYMENTS] b ON (a.[CustID] = b.[LINK ID])
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
WHERE b.[DATE]='20140925'

I get

2
If you take 618 you will see that the aggregation doesn't add up to the total of the unaggregated amounts
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Please look at Twinkle Toes school (CustID = 618)
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You have a repetition somewhere. You will notice that the second table is 2 or 3 times the total of the lower table.

Do you have 618 many times in a particular table?

It would help if you posted all of the data in the three tables relating to customer ID 618 to figure this out.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
There are two Children with CustomerID of 618 but I would have thought that this makes no difference
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I would suggest you delete your attached table from the web as a matter of urgency. I'm sure that there is detail in there that you are not meant to share. I will delete my copy at the end of the question.

CustomerID 618 has two children. Therefore, it will generate two rows per Payment Table, which will double the total payments.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this:

With MySchool
as
(SELECT a.[CustID] As [CUSTOMERS|CustID], c.School as School
FROM [CUSTOMERS] a 
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID]))

SELECT a.[CustID] As [CUSTOMERS|CustID], a.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] 
FROM [MySchool] a 
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
WHERE b.[DATE]='25 September 2014' 
GROUP BY a.[CustID], c.School

Open in new window


However, this will not succeed with CustomerID 39, who has 2 children at different schools.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. I am not sure how to delete the spreadsheet
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Therefore, I suggest this:

With MySchool
as
(SELECT a.[CustID] As [CUSTOMERS|CustID], Min(c.School) as School
FROM [CUSTOMERS] a 
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
GROUP BY a.[CustID])

SELECT a.[CustID] As [CUSTOMERS|CustID], a.School as School, Sum(b.[AMOUNT]) As [PAYMENTS|AMOUNT] 
FROM [MySchool] a 
LEFT JOIN [CHILDREN] c ON (a.[CustID] = c.[CustomerID])
WHERE b.[DATE]='25 September 2014' 
GROUP BY a.[CustID], c.School

Open in new window

0
PortletPaulfreelancerCommented:
I do wish this was listened to...

this should not be used
WHERE b.[DATE]='25 September 2014'


this should
WHERE b.[DATE]='20140925'
0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
But if date is stored like '25-Oct-2014' then how can we write "Where b.[DATE] ='20140925'

Because everyone is using date format with his/her own choice. I don't know what is the date format/style in other countries but here in Pakistan we are using "dd-MMM-yyyy".

I really appreciate comments/guidelines in response to correct me where I am wrong.

Regards
Muhammad Uzair
0
PortletPaulfreelancerCommented:
mmmm....

IF the field is of type Datetime, that means the data is stored as 2 integers

date/time data is NOT STORED IN A FORMAT, unless you are using varchar or nvarchar (which you shoul dnot do for dates)

suggested reading:
The ultimate guide to the datetime datatypes

Date and time formats
A common misconception is that SQL Server stores these datatypes in some particular readable format. That is not the case. SQL Server stores these values in an internal format (for instance two integers for datetime and smalldatetime). However, when you use T-SQL to specify a value (in an INSERT statement, for instance) you express it as a string. And there are rules for how different datetime string formats are interpreted by SQL Server. But note that SQL Server does not remember the format in any way.

I'm Australian (& not discussing the recent test match!)
so I also use dd/mm/yyyy  or dd/mmm/yyyy

but I repeat:

the safest date literal to use in your queries is YYYYMMDD
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for all the extra advice
0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
Dear Sir PortletPaul

Agreed! And I know that DATE and VARCHAR are two different data types. And absolutely we cannot use VARCHAR to store a date because by doing this we will have 29102014 as string/text not as a date.

Moreover, we can use DATE or TIMESTAMP(x) to store the date with time as per our requirement.

Now I want your expert opinion on this.  Below is the table structure of a table in Oracle Database 11g R1.

DESC myTable
Name                Null     Type                
------------------- -------- -------------------
ISSUED_ID           NOT NULL NUMBER              
ISSUEM_ID                    NUMBER              
REC_DETID                    NUMBER              
ISSUE_TYPE                   VARCHAR2(10)        
ISSUE_DATE                   DATE          

Here "ISSUE_DATE" has datatype "DATE" and I can get the required results if I would write

SELECT * FROM myTable
where ISSUE_DATE = '29-OCT-2014' because date stored in table as 'dd-MMM-yyyy' not YYYYMMDD

Any other method like TO_DATE is not working.

Looking forward for your expert opinion to correct myself that where I am doing wrong.
0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
Dear Sir PortletPaul

I am a Pakistani and neither I am discussing recent test match nor I have any interest in that.

I am just looking forward to update and correct myself to get opinion(s) from experts like you.

If you think I used any wrong word , please accept apologize.
0
PortletPaulfreelancerCommented:
Oh dear! I was merely making a joke... I am really sorry, I meant no offence.

Please don't take that comment literally, the Pakistan team outplayed Australia and fully deserved the win.
Younis Khan was in superb form.
(I was trying to indicate we didn't want to talk about it because we played so poorly)
----------------

Regarding the table with field of type date.

No matter what that field "looks like" it is stored as an integer. And the comment I made earlier remains true "the safest possible date literal" is YYYYMMDD.

You are free to use whatever format you like, but the best practice, safest, method is YYYYMMDD

"OCT" is dependent on a language setting; YYYYMMDD is recognized by SQL Server no matter what the language settings are
0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
Dear Sir PortletPaul

Many thanks

Are you available on FACEBOOK? May I connect you there ?
0
PortletPaulfreelancerCommented:
:) no I don't use facebook, you can use EE's message feature to contact me
0
Muhammad Uzair AwanOracle Programmer (Form6i / APEX)Commented:
What is that EE's Meassage Feature? Any special service only for Australia?

Any other source like WhatsApp?
0
PortletPaulfreelancerCommented:
click the link I provided, it is a standard feature of EE, available for messages between members
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.