Restore Database: complicated

Showing posts with label complicated. Show all posts

Thursday, March 29, 2012

Help with complicated SQL query

Hi everyone.

This is my scenario:

I have two tables:
persons (id,age,roleid)
roles (roleid,description)

I want to build a sql query to produce the following rows (example):
range(age) role1 role2 role3 ... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19

I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.

Thanks for all your help!

Rolandyou want a cross tab query using the CASE statement. Read about both in books online and come back if you are still having trouble.|||Lookup "Crosstab" in Books Online.|||this isn't quite as straightforward as it first appearsselect range
, sum(case when roleid =1
then rows else 0 end) as role1
, sum(case when roleid =2
then rows else 0 end) as role2
, ...
, sum(case when roleid =n
then rows else 0 end) as rolen
from (
select '0 to 4' as range
, roleid
, count(*) as rows
from persons
where age between 0 and 4
group by roleid
union all
select '5 to 9' as range
, roleid
, count(*) as rows
from persons
where age between 5 and 9
group by roleid
union all
select '10 to 14' as range
, roleid
, count(*) as rows
from persons
where age between 10 and 14
group by roleid
union all
select '15 to 20' as range
, roleid
, count(*) as rows
from persons
where age between 15 and 20
group by roleid
) as dt
group by range|||Great r937!! thanks!! everything worked perfectly!

Roland

Help with complicated splitting and inserting

Ok, I don't come here unless for desperation since my head is splitting thinking about this and not knowing SQL enough to so something this hidious, I need some help! Being a C# developer and NOT a DBA, I'm about to blow my head off. Excuse my french but it's a bad day when I have to do crap like this that should have been designed right the first time :).

Ok, bear with me.

Story: We have a Product table. Stupidly, before I was here at this company, in the product table we have a ProductDescription. In the product Description, there is HTML and text. the HTML contains the actual child ProductIDs that are related to the product record. Yea, and so here it goes.

Goal: Split out the product and it's related child IDs into our new ProductRelationship table which we should have done 1.5 years ago

Shortened Schema for posting purposes goes like this:

Product
-
ProductID
ProductDescription

ProductRelationship
-
ProductID
RelatedProductID
Description

Ok, so in the Product table, here's an example from ProductDescription:

'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970's and 1980's television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music. You can also purchase the individual tracks: 01. American Plains <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105235">WAV</a> 02. Sultry Summer Night <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105237">WAV</a> 03. Ocean Skyline <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105239">WAV</a> 04. Wistful Lover <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105241">WAV</a> 05. Final Choice <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105243">WAV</a> 06. Fun and Free <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105245">WAV</a> 07. Wayward Strangers <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105247">WAV</a> 08. Savored Moments <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105249">WAV</a> 09. Endless Searcher <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105251">WAV</a> 10. Bach Piano <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105253">WAV</a> 11. Fog Bound Mornings <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="http://links.10026.com/?link=ProductInfo.aspx?ProductID=105255">WAV</a> '

Ok, so, as you can see, the fu**ing product IDs are in the damn HTML! I need to take the current ProductID and it's corresponding ProductIDs found in this memo field and put into the ProductRelationship table like they sh ould have been in the first place!

In other words, I should get these records (assuming a ProductID of 100000 for this record) put into my ProductRelationship table for this example:

ProductID RelatedProductID
100000 105234
100000 105235
100000 105236
100000 105237
100000 105238
100000 105239
100000 105240
100000 105241
100000 105242
100000 105243
100000 105244
100000 105245
100000 105246
100000 105247
100000 105248
100000 105249
100000 105250
100000 105251
100000 105252
100000 105253
100000 105254
100000 105255
…..next product

How the hell do I even attack this? Should I just use C#?!?!?!? How would I do this in SQL first then maybe I'll try attacking this using maybe a C# form or console program. Hell if I know.

Hello,

You've sort of answered your own question I think. This is obviously going to involve string manipulation, so c# or similar would be better suited (especially if .net 2 has some sort of html parser you can use). This is not to say you can't do this in tsql, but it will be slow and ugly.

I see the ProductDescription also has single and double quotes embedded in the html, so will make working with tsql even more cumbersome.

My vote's in for c#...

Cheers,
Rob

|||

alright, thanks. I guess what I'll do is first to this in SQL as it's gonna take me longer to setup & Code in C# since I've in the past mostly done ASP.NET, not client-side data manipulation to this extent

so any help with my T-SQL here?

|||

-- Using a table of number as in
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
-- you can do this. Note that this assumes ProductID=NNNNN" and won't work if the format differs in any way.
-- This sort of thing is better done in your C# client.

SELECT ProductID,
SUBSTRING(ProductDescription,
Number+LEN('ProductID='),
CHARINDEX('"',ProductDescription,Number+LEN('ProductID='))-Number-LEN('ProductID=')) as RelatedProductID
FROM Product
INNER JOIN Numbers ON Number BETWEEN 1 AND LEN(ProductDescription)
WHERE SUBSTRING(ProductDescription,Number,LEN('ProductID='))='ProductID='

|||

Ok, thanks but I need to loop through my entire set of records and for each pull out the ProductID and it's ChildIDs for that record and repeat for each record. Insert as stated for each ProductID and it's corresponding Childs in our new ProductRelationship Table.

I possibly see 2 loops here. One iterates through my recordset and an internal iteration that then spits each ProductID/chid ProductID (from description) into our table.

but 2 nested loops is probably an inefficient way to code this.

Help with complicated query...

Hello All,
I have a simple table that stores messages of different types from
different sources. The definition of the table is shown below. I need
to devise an efficient query to return a "list of the N more recent
messages for a subset of sources within a specified time frame."
create table MessageTbl
(
src nvarchar(50), -- Source of the message
type nvarchar(50), -- Type of the message.
msg nvarchar(1000), -- Text of the message
dt datetime -- When the message was posted
)
We are given the following parameters:
1. declare @.startTime datetime -- Start of the time frame
2. declare @.endTime datetime -- End of the time frame
3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
contains a list of sources/types for which we want to obtain the
messages.
4. N -- How many messages per source/type
If all I wanted was the 20 more recent messages for source1/type1
between @.startTime and @.endTime, I could do something like:
SELECT TOP 20 *
FROM MessageTbl
WHERE (src ='source1') AND ( type='type1')
AND ( dt BETWEEN @.startTime AND @.endTime )
ORDER BY dt DESC
In my case, however, I have a number of pairs (src,type) in the local
table @.myTable. Therefore, what I ultimately want is equivalent to the
UNION of the results of such query for each pair(src,type). Another
thing missing is that I am using a hardcoded value for the TOP clause.
This also varies.
All the solutions that I can think of are very inneficient, cumbersome,
and involve a number of temporary tables. I was wondering if the
experts could lead me to a cleaner query design.
Thank you
- CDOn 25 Oct 2005 14:57:37 -0700, crbd98@.yahoo.com wrote:

>Hello All,
>I have a simple table that stores messages of different types from
>different sources. The definition of the table is shown below. I need
>to devise an efficient query to return a "list of the N more recent
>messages for a subset of sources within a specified time frame."
>create table MessageTbl
>(
> src nvarchar(50), -- Source of the message
> type nvarchar(50), -- Type of the message.
> msg nvarchar(1000), -- Text of the message
> dt datetime -- When the message was posted
> )
>We are given the following parameters:
>1. declare @.startTime datetime -- Start of the time frame
>2. declare @.endTime datetime -- End of the time frame
>3. declare @.myTable( src nvarchar(50), type nvarchar(50)) -- This table
>contains a list of sources/types for which we want to obtain the
>messages.
>4. N -- How many messages per source/type
>
>If all I wanted was the 20 more recent messages for source1/type1
>between @.startTime and @.endTime, I could do something like:
>SELECT TOP 20 *
>FROM MessageTbl
>WHERE (src ='source1') AND ( type='type1')
> AND ( dt BETWEEN @.startTime AND @.endTime )
>ORDER BY dt DESC
>In my case, however, I have a number of pairs (src,type) in the local
>table @.myTable. Therefore, what I ultimately want is equivalent to the
>UNION of the results of such query for each pair(src,type). Another
>thing missing is that I am using a hardcoded value for the TOP clause.
>This also varies.
>All the solutions that I can think of are very inneficient, cumbersome,
>and involve a number of temporary tables. I was wondering if the
>experts could lead me to a cleaner query design.
>Thank you
>- CD
Hi CD,
Try if this works:
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your reply. The query that you posted produces the
correct result but it is very expensive. It takes almost 30 seconds
when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
the execution plan and it seems there is a lot of table scanning going
on.
Maybe I have to look into a solution that does not use the join...
Any Ideas?
Thanks
- CD|||Are there any indexes? Your original DDL had no keys and you used a
proprietary table variable.
Also, you might want to fix the data element names. They are horrible.|||Hello Celko,
The table MessageTbl has a compound primary key involving src and type.
There are no other indexes. My local table variable has no keys and no
indexes. Do you think this is a problem? Is there any problem in using
table variables or are you just concerned about the portability of the
code?
Any suggestions?
Thank you
CD
--CELKO-- wrote:
> Are there any indexes? Your original DDL had no keys and you used a
> proprietary table variable.
> Also, you might want to fix the data element names. They are horrible.|||On 25 Oct 2005 16:22:34 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for your reply. The query that you posted produces the
>correct result but it is very expensive. It takes almost 30 seconds
>when MessageTbl has about 3600 rows and @.myTable has 51 rows. I checked
>the execution plan and it seems there is a lot of table scanning going
>on.
>Maybe I have to look into a solution that does not use the join...
>
>Any Ideas?
Hi CD,
Try if adding this index helps:
CREATE INDEX ProperNameHere
ON MessageTbl (src, type, dt DESC)
On 26 Oct 2005 10:50:55 -0700, crbd98@.yahoo.com wrote:

>Hello Celko,
>The table MessageTbl has a compound primary key involving src and type.
Huh? If there's a compount primary key on src and type, then how can you
find the 20 most recent messages between two moments for a given src and
type? As a result of the primary key, there will be only one message for
each src / type combination!!
But since you apparently have a PRIMARKY KEY that was not included in
your first post, please post the complete CREATE TABLE statement, WITH
all constraints, properties and indexes. My suggestion above might well
be invalidated by your current keys and indexes.

>There are no other indexes. My local table variable has no keys and no
>indexes. Do you think this is a problem?
You might have duplicates in the table variable, which will never
improve performance.
You can't define indexes for a table variable, but you can define
PRIMARY KEY or UNIQUE constraints (and they DO automatically add an
index). In your case, try if adding a PRIMARY KEY (src, type) helps the
performance. And if it doesn't, but doesn't hinder performance either,
then do leave it in.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for the suggestion of creating an index with the src, type,
and dt. That reduced the execution time from 30 sec to less than a
second. My last problem was to parameterize the value for TOP clause. I
created a huge dynamic SQL statement that I use with sp_executesql.
VERY UGLY!!! Do you have any alternative suggestion?
Thanks
Cassiano|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

> Hello Hugo,
> Thank you for the suggestion of creating an index with the src, type,
> and dt. That reduced the execution time from 30 sec to less than a
> second. My last problem was to parameterize the value for TOP clause. I
> created a huge dynamic SQL statement that I use with sp_executesql.
> VERY UGLY!!! Do you have any alternative suggestion?
> Thanks
> Cassiano
Issue a SET ROWCOUNT in the stored procedure:
CREATE PROCEDURE foo (@.nRows int, @.bar varchar(30))
AS
SET ROWCOUNT @.nRows
SELECT * FROM sysobjects where name <> @.bar
SET ROWCOUNT 0
GO
SQL Server specific, but it works, and there's no dynamic SQL or even
recompilation.|||On 28 Oct 2005 00:52:02 -0700, crbd98@.yahoo.com wrote:

>Hello Hugo,
>Thank you for the suggestion of creating an index with the src, type,
>and dt. That reduced the execution time from 30 sec to less than a
>second. My last problem was to parameterize the value for TOP clause. I
>created a huge dynamic SQL statement that I use with sp_executesql.
>VERY UGLY!!! Do you have any alternative suggestion?
>Thanks
>Cassiano
Hi Cassiano,
The suggestion made by Ross (SET ROWCOUNT) is fine if you wwant to limit
the total number of rows returned by the query. But I seem to recall
that your problem was more complex than that.
Going back in the thread, I see this query I posted a few days ago - is
this the one you are using, and where you want to replace TOP 20 with a
variable number?
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND a.dt IN (SELECT TOP 20 dt
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
ORDER BY c.dt DESC)
The easiest answer is to wait a few w

s. TOP @.variable will be
suppported in SQL Server 2000, which will hit the streets in the w

of
November 7th.
Or use the following (which is ANSI standard to boot):
SELECT a.src, a.type, a.msg, a.dt
FROM MessageTbl AS a
INNER JOIN @.myTable AS b
ON b.src = a.src
AND b.type = a.type
WHERE a.dt BETWEEN @.startTime AND @.endTime
AND (SELECT COUNT(*)
FROM MessageTbl AS c
WHERE c.src = a.src
AND c.type = a.type
AND c.dt BETWEEN @.startTime AND @.endTime
AND c.dt <= a.dt) <= 20
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you very much for your reply. This whole discussion has been very
enlightening.
I compared the performance of two variations of the solution:
#1. The last solution that you suggested.
#2. Based on your original solution (the one with hardcoded TOP
clause). The only modification that I made was to create the query
string dynamically to simmulate the effect of a variable TOP clause.
The table has the following indices (in addition to a PK index on a
MsgId column that I did not include in the posting).
CREATE INDEX IX_MessageTbl1
ON MessageTbl (src, type, dt DESC)
and
CREATE INDEX IX_MessageTbl2
ON MessageTbl (dt DESC)
I noticed that #1 was 6 times slower than #2. Although #2 is faster, I
do not like it, because I create the query dynamically. Do you know if
there is any index that I can create or any hint that I can use to
speed-up the query #1.
Thank you
- CDsql

Monday, March 19, 2012

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

Thursday, March 29, 2012

Help with complicated SQL query

Help with complicated splitting and inserting

Help with complicated query...

Monday, March 19, 2012

Help with a complicated query

Help with a complicated query

Restore Database

Blog Archive

About Me