Solved

# SQL Issue joining three tables

Posted on 2014-10-28
187 Views
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
Question by:murbro
• 6
• 6
• 5
• +1

LVL 24

Expert Comment

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
``````
0

Expert Comment

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
and a.CustId = c.CustomerId (+)
and b.Date = '25-SEP-2014'
GROUP BY a.CustId, c.School
0

LVL 48

Expert Comment

ID: 40408145

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

Author Comment

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

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

If you take 618 you will see that the aggregation doesn't add up to the total of the unaggregated amounts
0

Author Comment

ID: 40408378
Please look at Twinkle Toes school (CustID = 618)
0

LVL 24

Expert Comment

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

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

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

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
``````

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

Author Closing Comment

ID: 40408433
Thanks. I am not sure how to delete the spreadsheet
0

LVL 24

Expert Comment

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
``````
0

LVL 48

Expert Comment

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

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
0

LVL 48

Expert Comment

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)

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

ID: 40409946
Thanks for all the extra advice
0

Expert Comment

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

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

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

ID: 40410443
Dear Sir PortletPaul

Many thanks

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

LVL 48

Expert Comment

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

Expert Comment

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

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

## Featured Post

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.