Joining multiple tables in a view.

I have three tables

1^st table is Student

StudnetID (pk)

Other fields…

2^nd table is PhoneType

PhoneTypeID (pk)

PhoneType

3^rd table is StudentHasPhone

SHPID (pk)

StudnetID (fk)

PhoneTypeID (fk)

PhoneNumber

PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…

StudnetID: 1

Name: John

HomePhone: 123-456-7890

WorkPhone: 123-456-7890

CellPhone:

Pager: 123-456-7890

Fax:

Memo: This is one student record.

Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won't have.

Thanks in advanced,

Nathan Rover

What you need is a View with UNION ALL but your tables must be UNION compatible which means same data type facing the same direction. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

|||

You can join to the phone table multiple times, as follows:

SELECT StudentID, HP.PhoneNumber, WP.PhoneNumber, CP.PhoneNumber, Pg.PhoneNumber, Fx.PhoneNumber
FROM Student S
LEFT OUTER JOIN StudentHasPhone HP
ON S.StudentID = HP.StudentID
AND HP.PhoneTypeID = 1 --Home Phone
LEFT OUTER JOIN StudentHasPhone WP
ON S.StudentID = WP.StudentID
AND WP.PhoneTypeID = 2 --Work Phone
LEFT OUTER JOIN StudentHasPhone CP
ON S.StudentID = CP.StudentID
AND CP.PhoneTypeID = 3 --Cell Phone
LEFT OUTER JOIN StudentHasPhone Pg
ON S.StudentID = Pg.StudentID
AND Pg.PhoneTypeID = 4 --Pager
LEFT OUTER JOIN StudentHasPhone Fx
ON S.StudentID = Fx.StudentID
AND Fx.PhoneTypeID = 5 --Fax

BTW: StudentHasPhone is not a good table name. StudentPhone, or simply Phone, would be much better.

|||

Thanks, that was exactly what I was looking for… It worked perfect.

--Nathan

Joining 2 tables via 3rd table

I've been squeezing my noggin trying to figure this problem out with little
to show for it though I admit my SQL ability is dismal.
I have 3 tables as follows (greatly simplified here):
tblProperties
int ID
1001
1002
1003
tblOwners
int ID
2001
2002
2003
tblPropertyOwners
int ID int PropertyID (FK) int OwnerID (FK)
3001 1001 2001
3001 1001 2002
3002 1001 2003
3003 1003 2002
The tblPropertyOwners table indicates who owns which properties. Thus,
using the above sample data, property 1001 has 3 owners (2001, 2002, 2003),
property 1002 has zero owners (since no entries in the link table) and
property 1003 has one owner (2002).
The client wants a single record returned for each property that shows
property data plus data for up to two owners (if any) for that property. I'
m
loading the dataset into an ASP.Net 2.0 GridView and exporting it to an Exce
l
spreadsheet (which works great, now if only I could get the data correct!).
The output for the above sample data should be as follows:
PropertyID Owner1 Owner2
1001 2001 2003 <-- min & max ownerIDs for first
property though any 2 will do
1002 null null <-- this property has no
owners
1003 2002 <-- this property has one own
er
I've tried many different solutions but my SQL ability is basic and I'm
tired of wasting time on this so I'm looking for others' expertise. Any
suggestions or thoughts? I really appreciate your time.
Troy
.Net DeveloperSomething like:
select
p.ID
, min (po.OwnerID) Owner1
, max (po.OwnerID) Owner2
from
tblProperties p
left join
tblPropertyOwners po on po.PropertyID = p.ID
group by
p.ID
It will give you both columns - even if there isonly one owner.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> wrote in message
news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com...
I've been squeezing my noggin trying to figure this problem out with little
to show for it though I admit my SQL ability is dismal.
I have 3 tables as follows (greatly simplified here):
tblProperties
int ID
1001
1002
1003
tblOwners
int ID
2001
2002
2003
tblPropertyOwners
int ID int PropertyID (FK) int OwnerID (FK)
3001 1001 2001
3001 1001 2002
3002 1001 2003
3003 1003 2002
The tblPropertyOwners table indicates who owns which properties. Thus,
using the above sample data, property 1001 has 3 owners (2001, 2002, 2003),
property 1002 has zero owners (since no entries in the link table) and
property 1003 has one owner (2002).
The client wants a single record returned for each property that shows
property data plus data for up to two owners (if any) for that property.
I'm
loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
Excel
spreadsheet (which works great, now if only I could get the data correct!).
The output for the above sample data should be as follows:
PropertyID Owner1 Owner2
1001 2001 2003 <-- min & max ownerIDs for first
property though any 2 will do
1002 null null <-- this property has no
owners
1003 2002 <-- this property has one
owner
I've tried many different solutions but my SQL ability is basic and I'm
tired of wasting time on this so I'm looking for others' expertise. Any
suggestions or thoughts? I really appreciate your time.
Troy
.Net Developer|||select A.iID as IDProperty,
min(B.iIDOwner) as Owner1,
case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
else NULL end as Owner2
from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
ON A.iID = B.iIDProperty
group by A.iID
Martin C K Poon
Senior Analyst Programmer
====================================
"Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> bl
news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com g...
> I've been squeezing my noggin trying to figure this problem out with
little
> to show for it though I admit my SQL ability is dismal.
> I have 3 tables as follows (greatly simplified here):
> tblProperties
> int ID
> 1001
> 1002
> 1003
> tblOwners
> int ID
> 2001
> 2002
> 2003
> tblPropertyOwners
> int ID int PropertyID (FK) int OwnerID (FK)
> 3001 1001 2001
> 3001 1001 2002
> 3002 1001 2003
> 3003 1003 2002
> The tblPropertyOwners table indicates who owns which properties. Thus,
> using the above sample data, property 1001 has 3 owners (2001, 2002,
2003),
> property 1002 has zero owners (since no entries in the link table) and
> property 1003 has one owner (2002).
> The client wants a single record returned for each property that shows
> property data plus data for up to two owners (if any) for that property.
I'm
> loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
Excel
> spreadsheet (which works great, now if only I could get the data
correct!).
> The output for the above sample data should be as follows:
> PropertyID Owner1 Owner2
> 1001 2001 2003 <-- min & max ownerIDs for
first
> property though any 2 will do
> 1002 null null <-- this property has no
> owners
> 1003 2002 <-- this property has one
owner
> I've tried many different solutions but my SQL ability is basic and I'm
> tired of wasting time on this so I'm looking for others' expertise. Any
> suggestions or thoughts? I really appreciate your time.
> Troy
> .Net Developer
>|||That was my initial attempt but, alas, it doesn't work. If there is only on
e
owner for a property it will be listed as both Owner1 and Owner2, plus it
doesn't show properties with no owners. But thanks for the suggestion and
your time.
"Tom Moreau" wrote:

> Something like:
> select
> p.ID
> , min (po.OwnerID) Owner1
> , max (po.OwnerID) Owner2
> from
> tblProperties p
> left join
> tblPropertyOwners po on po.PropertyID = p.ID
> group by
> p.ID
> It will give you both columns - even if there isonly one owner.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> wrote in message
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com...
> I've been squeezing my noggin trying to figure this problem out with littl
e
> to show for it though I admit my SQL ability is dismal.
> I have 3 tables as follows (greatly simplified here):
> tblProperties
> int ID
> 1001
> 1002
> 1003
> tblOwners
> int ID
> 2001
> 2002
> 2003
> tblPropertyOwners
> int ID int PropertyID (FK) int OwnerID (FK)
> 3001 1001 2001
> 3001 1001 2002
> 3002 1001 2003
> 3003 1003 2002
> The tblPropertyOwners table indicates who owns which properties. Thus,
> using the above sample data, property 1001 has 3 owners (2001, 2002, 2003)
,
> property 1002 has zero owners (since no entries in the link table) and
> property 1003 has one owner (2002).
> The client wants a single record returned for each property that shows
> property data plus data for up to two owners (if any) for that property.
> I'm
> loading the dataset into an ASP.Net 2.0 GridView and exporting it to an
> Excel
> spreadsheet (which works great, now if only I could get the data correct!)
.
> The output for the above sample data should be as follows:
> PropertyID Owner1 Owner2
> 1001 2001 2003 <-- min & max ownerIDs for fir
st
> property though any 2 will do
> 1002 null null <-- this property has no
> owners
> 1003 2002 <-- this property has one
> owner
> I've tried many different solutions but my SQL ability is basic and I'm
> tired of wasting time on this so I'm looking for others' expertise. Any
> suggestions or thoughts? I really appreciate your time.
> Troy
> ..Net Developer
>|||YOU ARE THE SQL MASTER! Thanks so much for your time and effort. You've
saved us many hours of headbanging (we've wasted enough time on this problem
as is). I love programming but SQL refuses to stick to my synapses (give me
assembler over SQL any day).
Muchas gracias, Amigo.
Troy
.Net Developer, powered by a 16K TRS-80 Model III with cassette drive
"Martin C K Poon" wrote:

> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
> else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID
>
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> |b?l￥ó
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com ¤¤???g...
> little
> 2003),
> I'm
> Excel
> correct!).
> first
> owner
>
>|||Martin,
Expanding on my initial (simplified) request, what if tblOwners contains
other fields (e.g. varchar Name) that need to be displayed alongside the
owner IDs? Thus, the output for my sample code should be:
PropertyID O1ID O1Name O2ID O2Name
1001 2001 Name2001 2003 Name2003
1002 null null null null
1003 2002 Name2002 null null
Given tblOwners:
ID Name
2001 Name2001
2002 Name2002
2003 Name2003
The datset I am trying to gather actually contains many fields from
tblProperties (ID, name, address, phone, etc) and tblOwners (ID, name,
address, etc), thus joining to tblOwners complicates things a bit (for my
Jethro brain). Again, thanks for your time and effort. Take care.
Troy
"Martin C K Poon" wrote:

> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then max(B.iIDOwner)
> else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID
>
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Troy Dot Net" <TroyDotNet@.discussions.microsoft.com> |b?l￥ó
> news:3E794808-946A-41FB-A868-E0094FEB1A70@.microsoft.com ¤¤???g...
> little
> 2003),
> I'm
> Excel
> correct!).
> first
> owner
>
>|||
Untested but try this
select p.IDProperty,
t1.ID as O1ID,
t1.Name as O1Name,
t2.ID as O2ID,
t2.Name as O2Name
from (
select A.iID as IDProperty,
min(B.iIDOwner) as Owner1,
case when min(B.iIDOwner) <> max(B.iIDOwner) then
max(B.iIDOwner) else NULL end as Owner2
from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
ON A.iID = B.iIDProperty
group by A.iID ) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID=p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID=p.Owner2|||You, too, are an SQL Master.
Your SQL code using our naming convention:
select p.PID,
t1.ID as O1ID,
t1.Name as O1Name,
t2.ID as O2ID,
t2.Name as O2Name
from (
select A.ID as PID,
min(B.OwnerID) as Owner1,
case
when min(B.OwnerID) <> max(B.OwnerID) then
max(B.OwnerID)
else NULL
end as Owner2
from tblProperties A
LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
group by A.ID) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
ORDER BY P.PID
The output using our data (names changed):
PID O1ID O1Name O2ID O2Name
1 1 ABC Community 4 Riveria Communities
2 2 Chuck Norris 5 Dresden Associates
3 1 ABC Community NULL NULL
4 1 ABC Community NULL NULL
Using your code & Martin's, I'm confidant (faux confidence?) I can complete
the final requirement of joining to a fourth table (tblContacts via
tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
listed for each of the two owners, thus the header row will be PID, O1ID,
O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
contact for O1 (could be null) and C2 is the first contact for O2 (could be
null). I suppose I should have listed the full requirement from the outset
but I was afraid no one would tackle such a beast so I started small. :)
Anyway, thanks for your sharing your wisdom and time. Many thanks.
Troy
"markc600@.hotmail.com" wrote:

>
> Untested but try this
>
> select p.IDProperty,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.iID as IDProperty,
> min(B.iIDOwner) as Owner1,
> case when min(B.iIDOwner) <> max(B.iIDOwner) then
> max(B.iIDOwner) else NULL end as Owner2
> from tblProperties A LEFT OUTER JOIN tblPropertyOwners B
> ON A.iID = B.iIDProperty
> group by A.iID ) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID=p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID=p.Owner2
>|||My initial foray is close but not quite ready for prime time. The sample
record output below correctly shows contact for Owner1 but, unfortunately, i
t
shows Contact2 (Dan Linkletter) as being a contact for NULL Owner2. I'll
tweak it until I have it down pat. (Names changed in output.)
PID O1ID O1Name C1ID
C1Name
12 8 Richard Bushido Co. 5 Richard Placido NULL NULL 6 Dan Linkletter
My (slightly flawed) SQL is as follows:
select p.PID,
t1.ID as O1ID,
t1.Name as O1Name,
c1.ID as C1ID,
c1.FirstName + ' ' + c1.LastName as C1Name,
t2.ID as O2ID,
t2.Name as O2Name,
c2.ID as C2ID,
c2.FirstName + ' ' + c2.LastName as C2Name
from (
select A.ID as PID,
min(B.OwnerID) as Owner1,
min(C.ContactID) as Contact1,
case
when min(B.OwnerID) <> max(B.OwnerID) then
max(B.OwnerID)
else
NULL
end as Owner2,
case
when min(C.ContactID) <> max(C.ContactID) then
max(C.ContactID)
else
NULL
end as Contact2
from tblProperties A
LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
LEFT OUTER JOIN tblOwnerContacts C ON B.OwnerID = C.OwnerID
group by A.ID) p
LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
LEFT OUTER JOIN tblContacts c1 ON c1.ID = p.Contact1
LEFT OUTER JOIN tblContacts c2 ON c2.ID = p.Contact2
ORDER BY P.PID
"Troy Dot Net" wrote:
> You, too, are an SQL Master.
> Your SQL code using our naming convention:
> select p.PID,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.ID as PID,
> min(B.OwnerID) as Owner1,
> case
> when min(B.OwnerID) <> max(B.OwnerID) then
> max(B.OwnerID)
> else NULL
> end as Owner2
> from tblProperties A
> LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
> group by A.ID) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
> ORDER BY P.PID
> The output using our data (names changed):
> PID O1ID O1Name O2ID O2Name
> 1 1 ABC Community 4 Riveria Communities
> 2 2 Chuck Norris 5 Dresden Associates
> 3 1 ABC Community NULL NULL
> 4 1 ABC Community NULL NULL
> Using your code & Martin's, I'm confidant (faux confidence?) I can complet
e
> the final requirement of joining to a fourth table (tblContacts via
> tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
> listed for each of the two owners, thus the header row will be PID, O1ID,
> O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
> contact for O1 (could be null) and C2 is the first contact for O2 (could b
e
> null). I suppose I should have listed the full requirement from the outse
t
> but I was afraid no one would tackle such a beast so I started small. :)
> Anyway, thanks for your sharing your wisdom and time. Many thanks.
> Troy
> "markc600@.hotmail.com" wrote:
>|||FYI: I've decided to use brute force to generate the dataset I need (yes
Virginia, I was unable to figure out a good solution using a single query).
I'll use Mark's code to grab property, owner1 and owner2 data and stuff that
into a temp table. Then I'll grab contact1 data and stuff it into the same
temp table, then do the same for Contact2. Finally, I'll return the temp
table. The performance will likely suffer but I need a working solution NOW
.
Troy
"Troy Dot Net" wrote:
> You, too, are an SQL Master.
> Your SQL code using our naming convention:
> select p.PID,
> t1.ID as O1ID,
> t1.Name as O1Name,
> t2.ID as O2ID,
> t2.Name as O2Name
> from (
> select A.ID as PID,
> min(B.OwnerID) as Owner1,
> case
> when min(B.OwnerID) <> max(B.OwnerID) then
> max(B.OwnerID)
> else NULL
> end as Owner2
> from tblProperties A
> LEFT OUTER JOIN tblPropertyOwners B ON A.ID = B.PropertyID
> group by A.ID) p
> LEFT OUTER JOIN tblOwners t1 ON t1.ID = p.Owner1
> LEFT OUTER JOIN tblOwners t2 ON t2.ID = p.Owner2
> ORDER BY P.PID
> The output using our data (names changed):
> PID O1ID O1Name O2ID O2Name
> 1 1 ABC Community 4 Riveria Communities
> 2 2 Chuck Norris 5 Dresden Associates
> 3 1 ABC Community NULL NULL
> 4 1 ABC Community NULL NULL
> Using your code & Martin's, I'm confidant (faux confidence?) I can complet
e
> the final requirement of joining to a fourth table (tblContacts via
> tblOwnerContacts table) so that the first (minimum ID) contact (if any) is
> listed for each of the two owners, thus the header row will be PID, O1ID,
> O1Name, C1ID, C1Name, O2ID, O2Name, C2ID, C2Name where C1 is the first
> contact for O1 (could be null) and C2 is the first contact for O2 (could b
e
> null). I suppose I should have listed the full requirement from the outse
t
> but I was afraid no one would tackle such a beast so I started small. :)
> Anyway, thanks for your sharing your wisdom and time. Many thanks.
> Troy
> "markc600@.hotmail.com" wrote:
>

Joined table -- display in datagrid

Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.

Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it.

Any ideas on how to attack this?

You are saying you have the query working. Are you having problem displaying the data? If so I can move the post to Datagrid section where you have a better chance of receiving help.|||

ndinakar:

Are you having problem displaying the data?

I think its more sql based than grid based -- might be both (probably is). Here's an example.

Table - Cases : caseID (pk), CaseNumber, Notes
Table - Outcomes : outcomeID (pk), outcome
Table - CasesOutcome : caseID (fk), outcomeID (fk)

Data - Cases :
caseID : 1
CaseNumber : 2007xx45
Notes : (empty)

Data - Outcomes :
outcomeID : 1
outcome : guilty as charged by judge

outcomeID : 2
outcome : pled guilty to felony assault charge

outcomeID : 3
outcome : pled guilty to felony theft charge

Data - CasesOutcome:
caseID : 1
outcomeID : 2

caseID : 1
outcomeID : 3

When this is all said and done, a grid is generated with two rows, repeating the data (shows the case number twice) which is not really desired -- the outcome however is. Ideally the two of those items would show up under the gridview in 1 column (when being edited, they change to dropdowns) -- but I'm not sure if this is even possible.

|||Can you post the query you have, and the expected result.

Join Multiple Statements for view

Hello, I have the following view for the 3rd quarter I need to include all 4 quarters in this view. The only thing that will change is the qtr name (ie qt1, qtr2 etc) the sums and the date ranges, the % of target achieved will need to be at the end and calcuate all months. How can I join each of these queries for all quarters into one big one that will output each quarter in the view, ive tried but keep getting errors? - thanks for your help

SQL> CREATE VIEW campuscont_qtr3 AS
2 SELECT campus.campus,
3 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
4 (ROUND(campus.QTR3/tot_contr,2)*q3)/
5 campus.QTR3*100 "% OF TARGET ACHIEVED"
6 FROM campus,(SELECT SUM(AMOUNT) Q3
7 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
8 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
9 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3 % OF TARGET ACHIEVED
-- ---- -------
Main 396.9 79.38
East 194.4 77.76
West 97.2 77.76
North 121.5 81You can make use of the UNION / UNION ALL clause

CREATE VIEW CAMPUS_CONTR_ALL_QTR
AS

SELECT
1 AS QTR,
...the rest of your select concerning the first quarter

UNION ALL

SELECT
2 AS QTR,
...the rest of your select concerning the second quarter

and so on.|||OK. looks like this will work but IM getting the wrong output. I would only like one instance of campus and all the quarters to follow. Heres what the output I receieved.

SQL> SELECT campus.campus,
2 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3"
3 FROM campus,(SELECT SUM(AMOUNT) Q3
4 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
5 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
6 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
7 UNION ALL
8 SELECT campus.campus,
9 ROUND(campus.QTR2/tot_contr,2)*q2 "QTR2"
10 FROM campus,(SELECT SUM(AMOUNT) Q2
11 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
12 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q2contr,
13 (SELECT SUM(QTR2) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3
-- ----
Main 396.9
East 194.4
West 97.2
North 121.5
Main 380.7
East 234.9
West 97.2
North 97.2|||Let's get this straight. The UNION of selects should have returned the following result :

CAMPUS QTR CONTRIBUTION
====================
Main 1 396.9
East 1 194.4
West 1 97.2
North 1 121.5
Main 2 380.7
East 2 234.9
West 2 97.2
North 2 97.2

Now what you actually would like is something like
CAMPUS QTR1 QTR2 QTR3
=====================
Main 396.9 380.7
East 194.4 234.9
West 97.2 97.2
North 121.5 97.2

Is that what you want ?

BTW, I assume that the date range in your latest select for Q2 is a typo...|||Yes, just what I wanted. How would I do it?
- thanks|||OK. To achieve this, your first query is looking good, and should be modified only slightly.

I can only suggest to use the INNER JOIN, LEFT OUTER JOIN, etc. syntax. It makes the query much easier to read.

Your final select would look like this

SELECT campus.campus,
ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
(ROUND(campus.QTR3/tot_contr,2)*q3)/
campus.QTR3*100 "% OF TARGET ACHIEVED"

/* CAMPUS IS YOUR DRIVING TABLE */
FROM campus

/* inner join on 1st qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q1
FROM contribution wHERE CDATE >= TO_DATE('01/01/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('31/03/03', 'MM/DD/YY')) Q1contr
ON 1 = 1

/* inner join on 2ND qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q2
FROM contribution wHERE CDATE >= TO_DATE('01/04/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('30/06/03', 'MM/DD/YY')) Q2contr
ON 1 = 1

etc..

/* inner join on total contribution */
INNER JOIN
(SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
ON 1 = 1;


Just out of curiosity. Isn't there any relationship between "Contributions" and "Campus" (like a CAMPUS column in table "Contributions") ?