Help with a complicated query

I have 4 tables, we'll name them HomeAddress, WorkAddress, Home2Address,
Work2Address. They all link to one table, Client. As you can probably
tell, this is not the best way to set up the database, so I am moving the
data from the 4 tables into one Address table. What I need to do, though,
is there is a field in each of the 4 tables labeled "preferred", meaning
it's the preferred address to use. Ideally, there would be only one
Preferred column marked True for each client, although that may not be the
case in the existing data. I need to do a query that would include the
preferred column from all 4 tables, that would
result in the 4 Preferred fields from each table for every client in the
client table. Can I do that in one query and if so, how?On Thu, 9 Dec 2004 12:35:51 -0700, "Rock" <rockisland@.yahoo.com>
wrote:
>I have 4 tables, we'll name them HomeAddress, WorkAddress, Home2Address,
>Work2Address. They all link to one table, Client. As you can probably
>tell, this is not the best way to set up the database, so I am moving the
>data from the 4 tables into one Address table. What I need to do, though,
>is there is a field in each of the 4 tables labeled "preferred", meaning
>it's the preferred address to use. Ideally, there would be only one
>Preferred column marked True for each client, although that may not be the
>case in the existing data. I need to do a query that would include the
>preferred column from all 4 tables, that would
>result in the 4 Preferred fields from each table for every client in the
>client table. Can I do that in one query and if so, how?
I think you want something like
update NewAddress set preferred='Home' where clientID in
(select clientID from HomeAddress where preferred=True)
Run the four versions of this sequentially, and live with the results.
With a little work you could merge the four into one, but what the
heck.
J.|||Well, maybe that will work, if the data is already correct in the first
place. But if I set a client to Home as the preferred, and then another
update changes that, I wouldn't know it and could end up with incorrect
data. So I need to get the results of the current data first, and was
hoping to be able to display the ClientID along with the preferred column
from each table, so that at a glance I can tell not only which ones are the
preferred, but will also be able to tell those that have no preferred method
or those that have more than one. Then I can notify my customer and let him
choose how to fix the data so that each client has one and only one
preferred address. Does that make sense?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:qtahr0l1viu7g8f9ng15fl0a2nik4n6jao@.4ax.com...
> On Thu, 9 Dec 2004 12:35:51 -0700, "Rock" <rockisland@.yahoo.com>
> wrote:
> >I have 4 tables, we'll name them HomeAddress, WorkAddress, Home2Address,
> >Work2Address. They all link to one table, Client. As you can probably
> >tell, this is not the best way to set up the database, so I am moving the
> >data from the 4 tables into one Address table. What I need to do,
though,
> >is there is a field in each of the 4 tables labeled "preferred", meaning
> >it's the preferred address to use. Ideally, there would be only one
> >Preferred column marked True for each client, although that may not be
the
> >case in the existing data. I need to do a query that would include the
> >preferred column from all 4 tables, that would
> >result in the 4 Preferred fields from each table for every client in the
> >client table. Can I do that in one query and if so, how?
> I think you want something like
> update NewAddress set preferred='Home' where clientID in
> (select clientID from HomeAddress where preferred=True)
> Run the four versions of this sequentially, and live with the results.
> With a little work you could merge the four into one, but what the
> heck.
> J.
>|||SELECT
cl.ClientID,
a.preferred As home_preferred,
b.preferred as work_preferred,
c.preferred as home2_preferred,
d.preferred as work2_preferred
FROM
client cl
LEFT JOIN homeaddress a
on cl.clientid = a.clientid
LEFT JOIN workaddress b
on cl.clientid = b.clientid
LEFT JOIN home2address c
on cl.clientid = c.clientid
LEFT JOIN work2address d
on cl.clientid = d.clientid
"Rock" <rockisland@.yahoo.com> wrote in message
news:%234dhrli3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Well, maybe that will work, if the data is already correct in the first
> place. But if I set a client to Home as the preferred, and then another
> update changes that, I wouldn't know it and could end up with incorrect
> data. So I need to get the results of the current data first, and was
> hoping to be able to display the ClientID along with the preferred column
> from each table, so that at a glance I can tell not only which ones are
the
> preferred, but will also be able to tell those that have no preferred
method
> or those that have more than one. Then I can notify my customer and let
him
> choose how to fix the data so that each client has one and only one
> preferred address. Does that make sense?
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:qtahr0l1viu7g8f9ng15fl0a2nik4n6jao@.4ax.com...
> > On Thu, 9 Dec 2004 12:35:51 -0700, "Rock" <rockisland@.yahoo.com>
> > wrote:
> > >I have 4 tables, we'll name them HomeAddress, WorkAddress,
Home2Address,
> > >Work2Address. They all link to one table, Client. As you can probably
> > >tell, this is not the best way to set up the database, so I am moving
the
> > >data from the 4 tables into one Address table. What I need to do,
> though,
> > >is there is a field in each of the 4 tables labeled "preferred",
meaning
> > >it's the preferred address to use. Ideally, there would be only one
> > >Preferred column marked True for each client, although that may not be
> the
> > >case in the existing data. I need to do a query that would include the
> > >preferred column from all 4 tables, that would
> > >result in the 4 Preferred fields from each table for every client in
the
> > >client table. Can I do that in one query and if so, how?
> >
> > I think you want something like
> >
> > update NewAddress set preferred='Home' where clientID in
> > (select clientID from HomeAddress where preferred=True)
> >
> > Run the four versions of this sequentially, and live with the results.
> >
> > With a little work you could merge the four into one, but what the
> > heck.
> >
> > J.
> >
>|||create table #mypref
(clientId char(8),
pref char(8))
insert into #mypref
select clientID,'home'
from homeaddress
where preferred=True
union
select clientID,'work'
from workaddress
where preferred=True
union
select clientID,'home2'
from homeaddress2
where preferred=True
union
select clientID,'work2'
from workaddress2
where preferred=True
-- those with multiples
select * from #mypref
where clientId in
(select clientId from #mypref
group by clientId
having count(*)>1)
-- those with none
select cl.clientId from client
where clientId not in
(select clientID from #mypref)
/* except there are always bugs with NOT IN */
J.
On Thu, 9 Dec 2004 13:25:27 -0800, "Jeff Williams"
<jwilliams@.nospam.com> wrote:
>SELECT
> cl.ClientID,
> a.preferred As home_preferred,
> b.preferred as work_preferred,
> c.preferred as home2_preferred,
> d.preferred as work2_preferred
>FROM
> client cl
> LEFT JOIN homeaddress a
> on cl.clientid = a.clientid
> LEFT JOIN workaddress b
> on cl.clientid = b.clientid
> LEFT JOIN home2address c
> on cl.clientid = c.clientid
> LEFT JOIN work2address d
> on cl.clientid = d.clientid
>"Rock" <rockisland@.yahoo.com> wrote in message
>news:%234dhrli3EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> Well, maybe that will work, if the data is already correct in the first
>> place. But if I set a client to Home as the preferred, and then another
>> update changes that, I wouldn't know it and could end up with incorrect
>> data. So I need to get the results of the current data first, and was
>> hoping to be able to display the ClientID along with the preferred column
>> from each table, so that at a glance I can tell not only which ones are
>the
>> preferred, but will also be able to tell those that have no preferred
>method
>> or those that have more than one. Then I can notify my customer and let
>him
>> choose how to fix the data so that each client has one and only one
>> preferred address. Does that make sense?
>>
>> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>> news:qtahr0l1viu7g8f9ng15fl0a2nik4n6jao@.4ax.com...
>> > On Thu, 9 Dec 2004 12:35:51 -0700, "Rock" <rockisland@.yahoo.com>
>> > wrote:
>> > >I have 4 tables, we'll name them HomeAddress, WorkAddress,
>Home2Address,
>> > >Work2Address. They all link to one table, Client. As you can probably
>> > >tell, this is not the best way to set up the database, so I am moving
>the
>> > >data from the 4 tables into one Address table. What I need to do,
>> though,
>> > >is there is a field in each of the 4 tables labeled "preferred",
>meaning
>> > >it's the preferred address to use. Ideally, there would be only one
>> > >Preferred column marked True for each client, although that may not be
>> the
>> > >case in the existing data. I need to do a query that would include the
>> > >preferred column from all 4 tables, that would
>> > >result in the 4 Preferred fields from each table for every client in
>the
>> > >client table. Can I do that in one query and if so, how?
>> >
>> > I think you want something like
>> >
>> > update NewAddress set preferred='Home' where clientID in
>> > (select clientID from HomeAddress where preferred=True)
>> >
>> > Run the four versions of this sequentially, and live with the results.
>> >
>> > With a little work you could merge the four into one, but what the
>> > heck.
>> >
>> > J.
>> >
>>
>