473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

crazy inner join

need some help with some sql I can't seem to get the syntax right. I've tried several but to no avail the one below gets me the
closest but I can't get the guardians records pulled.

SELECT SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID

I would like the results to show the records from the guardian table as well

tables:
GUARDIAN
fields:
ID
guardian1_first
guardian1_last

CHILD
fields:
ID
child_ID
first_name
last_name
tuition

relationship: guardian.ID = child.child_ID

results should show:

guardian1_first, guardian1_last, and the SUM(child.tuition) for all children where the child_ID is equal to the ID of guardian
listed

any help would be much appreciated, I've been driving myself mad with this
Nov 13 '05 #1
3 1480


jj****@earthlink.net wrote:
need some help with some sql I can't seem to get the syntax right. I've tried several but to no avail the one below gets me the
closest but I can't get the guardians records pulled.

SELECT SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID

I would like the results to show the records from the guardian table as well

tables:
GUARDIAN
fields:
ID
guardian1_first
guardian1_last

CHILD
fields:
ID
child_ID
first_name
last_name
tuition

relationship: guardian.ID = child.child_ID

results should show:

guardian1_first, guardian1_last, and the SUM(child.tuition) for all children where the child_ID is equal to the ID of guardian
listed

any help would be much appreciated, I've been driving myself mad with this


You're on the right track, but you need to add a group by clause if you
want to get a separate sum for each guardian rather than the single sum
for all guardians that you'll be seeing at the moment. Barring any
typos, this ought to work for you:

SELECT guardian1_first, guardian1_last, SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID
GROUP BY guardian.ID;

Nov 13 '05 #2
<jj****@earthlink.net> wrote in message news:<LF*****************@newsread1.news.pas.earth link.net>...
... I can't get the guardians records pulled.

SELECT SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID

I would like the results to show the records from the guardian table as well

tables:
GUARDIAN (ID, guardian1_first, guardian1_last)
CHILD (ID, child_ID, first_name, last_name, tuition)

relationship: guardian.ID = child.child_ID

results should show:
guardian1_first, guardian1_last, and the SUM(child.tuition) for all children where the child_ID is equal to the ID of guardian
listed


You're almost there:
SELECT guardian1_first, guardian1_last, SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID
GROUP BY guardian1_first, guardian1_last
Nov 13 '05 #3
DFS
jj****@earthlink.net wrote:
need some help with some sql I can't seem to get the syntax right.
I've tried several but to no avail the one below gets me the closest
but I can't get the guardians records pulled.

SELECT SUM(tuition)
FROM child INNER JOIN guardian ON guardian.ID = child.child_ID

I would like the results to show the records from the guardian table
as well

tables:
GUARDIAN
fields:
ID
guardian1_first
guardian1_last

CHILD
fields:
ID
child_ID
first_name
last_name
tuition

relationship: guardian.ID = child.child_ID

results should show:

guardian1_first, guardian1_last, and the SUM(child.tuition) for all
children where the child_ID is equal to the ID of guardian listed

any help would be much appreciated, I've been driving myself mad with
this


In addition to the good answers the others gave, I suggest renaming a couple
of your fields:
* guardian.ID to guardian.guardian_ID
* child.ID to child.child_ID.
* child.child_ID to child.guardian_ID.

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ike | last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL 4.0.20-standard. Thus, in trying to expedite the query, I am trying to set indexes in my tables. My query requires four...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
4
by: Nathan | last post by:
I have an application that uses an Access database to gather information on students' test scores. In the database there are three tables which are joined by one- to-many relationships: ...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
3
by: Zeff | last post by:
Hi all, I have a relational database, where all info is kept in separate tables and just the id's from those tables are stored in one central table (tblMaster)... I want to perform a query, so...
12
by: Chamnap | last post by:
Hello, everyone I have one question about the standard join and inner join, which one is faster and more reliable? Can you recommend me to use? Please, explain me... Thanks Chamnap
1
by: teneesh | last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this...
2
by: MATTXtwo | last post by:
I have this store procedure to select data from table with join like this...SELECT tblPeribadi.Personel_No, tblPeribadi.Nama,tblCompany.Keterangan as Company_Code, tblPeribadi.Jawatan,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.