Solved

SQL Issue joining three tables

Posted on 2014-10-28
22
187 Views
Last Modified: 2014-10-29
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
0
Comment
Question by:murbro
  • 6
  • 6
  • 5
  • +1
22 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40407940
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
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40408052
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40408145
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:murbro
ID: 40408373
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
 

Author Comment

by:murbro
ID: 40408378
Please look at Twinkle Toes school (CustID = 618)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408383
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
 

Author Comment

by:murbro
ID: 40408422
There are two Children with CustomerID of 618 but I would have thought that this makes no difference
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408424
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40408427
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
 

Author Closing Comment

by:murbro
ID: 40408433
Thanks. I am not sure how to delete the spreadsheet
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408435
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409630
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
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40409918
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409936
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
 

Author Comment

by:murbro
ID: 40409946
Thanks for all the extra advice
0
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40409952
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
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40409956
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409968
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
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40410443
Dear Sir PortletPaul

Many thanks

Are you available on FACEBOOK? May I connect you there ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40410481
:) no I don't use facebook, you can use EE's message feature to contact me
0
 

Expert Comment

by:Muhammad Uzair Awan
ID: 40412450
What is that EE's Meassage Feature? Any special service only for Australia?

Any other source like WhatsApp?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40412463
click the link I provided, it is a standard feature of EE, available for messages between members
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question