Solved

SQL Issue joining three tables

Posted on 2014-10-28
22
178 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:murbro
Comment Utility
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
Comment Utility
Please look at Twinkle Toes school (CustID = 618)
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks. I am not sure how to delete the spreadsheet
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for all the extra advice
0
 

Expert Comment

by:Muhammad Uzair Awan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Dear Sir PortletPaul

Many thanks

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

Expert Comment

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

Expert Comment

by:Muhammad Uzair Awan
Comment Utility
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
Comment Utility
click the link I provided, it is a standard feature of EE, available for messages between members
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now