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