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.<br><br><span class='product-name-no-link'>You can also purchase the individual tracks:</span><br><br>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><br>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><br>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><br>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><br>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><br>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><br>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><br>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><br>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><br>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><br>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><br>'

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 a Join and Duplicates?

Hi All,

I am banging my head against a brick wall over this problem, so any
help in the correct direction would be muchly appreciated!

I have 2 SQL (MS SQL) server tables, realated to -
a Property,
Sales of that property.

A property is uniquely identifed by its Roll, valuation Number and
Suffix (not my choosing).

Each property can only appear in the property table once, and can only
have 1 assessment - but can have multiple sales (ie - over the
annalysis period the same property can sell more than once).

There is approximatly 19000 properties relating to about 8000 sales.

When creating a query to list property and most recent sale (if there
is any) I end up with somthing like this -
SELECT [roll], [valuation], [suffix], [sale date]
FROM [property]
LEFT JOIN [sales]
ON
[property].[roll] = [sales].[roll] AND
[property].[valuation] = [sales].[valuation] AND
[property].[suffix] = [sales].[suffix]
(table names simplifed).

I get rows where there is all the property data there, but sale date
(etc.) is null (as I would expect from a left join), but the problem is
- when there is more than 1 sale for a property it pulls out another
copy of the property data.

In short, because of that I come out with more records than properties.

ie -
roll valuation suffix sale date
12 456789 A 1/1/2003
12 788988 B NULL
14 123456 A 1/1/2003
14 123456 A 1/1/2004
(Note - the last two are the same property).

I didn't know that the left join can affect both joined tables!

Is there any way around this? Any suggestions/hints in the right
direction would be very much appreciated!

THANKS!On 11 Apr 2005 21:27:53 -0700, "MaxPenguin"
<malcolm.lockyer@.origen.co.nz> wrote:

>Hi All,
>
>I am banging my head against a brick wall over this problem, so any
>help in the correct direction would be muchly appreciated!
>
>I have 2 SQL (MS SQL) server tables, realated to -
>a Property,
>Sales of that property.
>
>A property is uniquely identifed by its Roll, valuation Number and
>Suffix (not my choosing).
>Each property can only appear in the property table once, and can only
>have 1 assessment - but can have multiple sales (ie - over the
>annalysis period the same property can sell more than once).
>There is approximatly 19000 properties relating to about 8000 sales.
>
>When creating a query to list property and most recent sale (if there
>is any) I end up with somthing like this -
>SELECT [roll], [valuation], [suffix], [sale date]
>FROM [property]
>LEFT JOIN [sales]
>ON
>[property].[roll] = [sales].[roll] AND
>[property].[valuation] = [sales].[valuation] AND
>[property].[suffix] = [sales].[suffix]
>(table names simplifed).
>I get rows where there is all the property data there, but sale date
>(etc.) is null (as I would expect from a left join), but the problem is
>- when there is more than 1 sale for a property it pulls out another
>copy of the property data.
>In short, because of that I come out with more records than properties.
>ie -
>roll valuation suffix sale date
>12 456789 A 1/1/2003
>12 788988 B NULL
>14 123456 A 1/1/2003
>14 123456 A 1/1/2004
>(Note - the last two are the same property).
>
>I didn't know that the left join can affect both joined tables!
>
>Is there any way around this? Any suggestions/hints in the right
>direction would be very much appreciated!
>THANKS!

Are you able to tell us what want the query to return?
(And why?)|||It isn't really a matter of the left join "affecting both tables". For
any join that you use, if you have a many to one relationship then you
are going to get multiple rows for the "one" side since the join
effectively creates a cartesian product to start with. Whittling that
down is a matter of your join criteria.

In your description of the problem you state that you want the "most
recent sale" but there is nothing in your query to try to limit the
results to the most recent sale. Either of the queries below should
work. I usually see better performance using the LEFT JOIN/IS NOT NULL
method, but using NOT EXISTS is a bit more readable/logical in my
opinion.

SELECT p.roll, p.valuation, p.suffix, s.[sale date]
FROM property p
LEFT JOIN sales s ON p.roll = s.roll
AND p.valuation = s.valuation
AND p.suffix = s.suffix
LEFT JOIN sales s2 ON s2.roll = s.roll
AND s2.valuation = s.valuation
AND s2.suffix = s.suffix
AND s2.[sale date] > s.[sale date]
WHERE s2.roll IS NULL

SELECT p.roll, p.valuation, p.suffix, s.[sale date]
FROM property p
LEFT JOIN sales s ON p.roll = s.roll
AND p.valuation = s.valuation
AND p.suffix = s.suffix
WHERE NOT EXISTS (SELECT *
FROM sales s2
WHERE s2.valuation = s.valuation
AND s2.suffix = s.suffix
AND s2.roll = s.roll
AND s2.[sale date] > s.[sale date])

Both methods assume that you cannot have two sales on the same exact
date for the same property. They both effectively remove any rows where
there is another row for the same property with a later sale date. That
will leave only those rows with the latest (most recent) sale date.

HTH,
-Tom.|||Thomas R. Hummel wrote:
> In your description of the problem you state that you want the "most
> recent sale" but there is nothing in your query to try to limit the
> results to the most recent sale. Either of the queries below should
> work. I usually see better performance using the LEFT JOIN/IS NOT
NULL
> method, but using NOT EXISTS is a bit more readable/logical in my
> opinion.

Thanks your solution works perfectly. Thats exactly what I needed
really - a way to tell it that I did just want the most recent sales.

Thanks again :)!

> SELECT p.roll, p.valuation, p.suffix, s.[sale date]
> FROM property p
> LEFT JOIN sales s ON p.roll = s.roll
> AND p.valuation = s.valuation
> AND p.suffix = s.suffix
> LEFT JOIN sales s2 ON s2.roll = s.roll
> AND s2.valuation = s.valuation
> AND s2.suffix = s.suffix
> AND s2.[sale date] > s.[sale date]
> WHERE s2.roll IS NULL
> SELECT p.roll, p.valuation, p.suffix, s.[sale date]
> FROM property p
> LEFT JOIN sales s ON p.roll = s.roll
> AND p.valuation = s.valuation
> AND p.suffix = s.suffix
> WHERE NOT EXISTS (SELECT *
> FROM sales s2
> WHERE s2.valuation = s.valuation
> AND s2.suffix = s.suffix
> AND s2.roll = s.roll
> AND s2.[sale date] > s.[sale date])
> Both methods assume that you cannot have two sales on the same exact
> date for the same property. They both effectively remove any rows
where
> there is another row for the same property with a later sale date.
That
> will leave only those rows with the latest (most recent) sale date.
> HTH,
> -Tom.

HELP w/ multiple table join on foreign keys

I'm scratching my head, not a sql expert so i need help w/ the following:

i have three tables in Access:
airportNames, facililtyID, facilityNames

i'm using facilityID as the intermediate table to join above tables.
i'm passing a value to the WHERE clause in my sql statement comparing airportNames.strCode.

Here's the sql that doesn't work, it generates an error:

SELECT facilityNames.strName, airportNames.strName, airportNames.strCode FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames)
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames)
WHERE airportNames.strCode = 'ATL'

I know this can be done but i can't get it to work:eek:what does "can't get it to work" mean? does your query cause the server to crash? does the query actually run? if not, does it produce an error message? if so, what is the error message? if it runs, does it return any rows? no rows? the wrong rows?

please show your table layouts, and if possible, a few rows of sample data from each table|||in SQL Analyzer (a freebie) i get a dialog that says Error Number: -2147217900|||please show your table layouts, and if possible, a few rows of sample data from each table|||Jet (the database engine that underlies Microsoft Access and other tools) can be charitably described as tempermental. Does it work if you use:SELECT facilityNames.strName, airportNames.strName, airportNames.strCode
FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames))
WHERE airportNames.strCode = 'ATL'-PatP|||pat, you're a genius

but it's probably not jet being finicky, it's probably that we have our table and column names bacwards

there is no table called facilityID, that's a column used to join tables

his syntax is all wrong, and i tried to fix it and got confused

i cannot guess which columns the tables should be joined on without some more info|||I was just working from the original posting... I don't have a clue what they've really got, but at least what they posted was consistent even if it may not have been correct.

-PatP|||Here's what the three tables involved look like in structure and data:

Table airportNames

IDAirportNames | strName | strCode
1 | Hartsfield | ATL
2 | Birmingham | BHM

Table facilityID

IDFacility | IDFacilityNames | IDAirportNames
1 | 1 | 1
2 | 0 | 1

Table facilityNames

IDFacilityNames | strName
1 | XYZ facility

Does this help?? FYI, I'm using access 2000.|||Pat Phelan, tried your query but i still get an error. man, either access is a pos, this sql analyzer thing is a pos or both. i need to get this thing working:(|||SELECT facilityNames.strName
, airportNames.strName
, airportNames.strCode
FROM (
airportNames
INNER
JOIN facilityID
on facilityID.IDAirportNames
= airportNames.IDAirportNames
)
INNER
JOIN facilityNames
ON facilityNames.IDFacilityNames
= facilityID.IDFacilityNames
WHERE airportNames.strCode = 'ATL'|||dude, i've been sweating this for a couple of days, thanks for you time on this, if you're ever doing any Flash Actionscript work and need some help, i'm all ears...paul at mediacurrent dot com.

suffice it to say that your sql statement returned exactly what i'd been looking for. now i need to do a full data migration and see what happens...stay tuned. BTW, can i use the INNER JOIN statement in SQL Server or does it only accept JOIN?|||INNER is an optional keyword, JOIN == INNER JOIN

if you migrate this to sql server, you may want to remove the microsoft access parentheses from the FROM clause

flash actionscript? thanks for the offer, but (a) i'm not quite ready to tackle flash yet, and (b) i cannot think of anything that i'd use it for|||one more question, i've read some stuff on indexing foreign keys for better join performance. would you recommend this? can a foreign key be indexed if duplicate keys exist in the column?|||yes, do create indexes on foreign keys

and yes, they can

Help Setting Up A Linked Server to Query Directory Services

I'm not sure what I'm doing wrong here but I've been banging my head against
for a few hours now.
Background:
SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
Domain
MSSQLSVR service and SQL Agent are running as a Domain Administrator
Ive tried to create a linked server with "OLE DB Provider for Microsoft
Directory Services" as the data source. Ive tried this via Query Analylzer
and via Enterprise Manager. The result is the same:
select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
where objectCategory="person" and objectClass="users" ')
Gives this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
I've tried to create it this way via QA:
sp_addlinkedserver 'AD', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
and then in EM, Ive played with all the security stuff but it's still a no
go. I'm completely at my limit with this. Can anyone offer a suggestion?
Thanks in advance.
Errol NealErrol Neal wrote:
> I'm not sure what I'm doing wrong here but I've been banging my head against
> for a few hours now.
> Background:
> SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
> Domain
> MSSQLSVR service and SQL Agent are running as a Domain Administrator
> Ive tried to create a linked server with "OLE DB Provider for Microsoft
> Directory Services" as the data source. Ive tried this via Query Analylzer
> and via Enterprise Manager. The result is the same:
> select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> where objectCategory="person" and objectClass="users" ')
> Gives this error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
> returned 0x80040e14].
> I've tried to create it this way via QA:
> sp_addlinkedserver 'AD', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
> and then in EM, Ive played with all the security stuff but it's still a no
> go. I'm completely at my limit with this. Can anyone offer a suggestion?
> Thanks in advance.
> Errol Neal
Hi
I don't know if it helps, but try to alter your OPENQUERY statement a
little bit.
The following works for me and compared to yours I've only specified the
AD servername and not with DC= as you have.
select * from openquery
(
ADSI,'SELECT name, mail, cn
FROM ''LDAP://dc-1''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
Regards
Steen Schlüter Persson
DBA|||Thanks a lot! Syntax was defintely an issue. Using single quotes instead of a
double quote. I appreciate the reply!
"Steen Persson (DK)" wrote:
> Errol Neal wrote:
> > I'm not sure what I'm doing wrong here but I've been banging my head against
> > for a few hours now.
> > Background:
> >
> > SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
> > Domain
> > MSSQLSVR service and SQL Agent are running as a Domain Administrator
> >
> > Ive tried to create a linked server with "OLE DB Provider for Microsoft
> > Directory Services" as the data source. Ive tried this via Query Analylzer
> > and via Enterprise Manager. The result is the same:
> >
> > select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> > where objectCategory="person" and objectClass="users" ')
> >
> > Gives this error:
> >
> > Server: Msg 7321, Level 16, State 2, Line 1
> > An error occurred while preparing a query for execution against OLE DB
> > provider 'ADSDSOObject'.
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
> > returned 0x80040e14].
> >
> > I've tried to create it this way via QA:
> >
> > sp_addlinkedserver 'AD', 'Active Directory Service
> > Interfaces', 'ADSDSOObject', 'adsdatasource'
> >
> > and then in EM, Ive played with all the security stuff but it's still a no
> > go. I'm completely at my limit with this. Can anyone offer a suggestion?
> >
> > Thanks in advance.
> >
> > Errol Neal
> Hi
> I don't know if it helps, but try to alter your OPENQUERY statement a
> little bit.
> The following works for me and compared to yours I've only specified the
> AD servername and not with DC= as you have.
> select * from openquery
> (
> ADSI,'SELECT name, mail, cn
> FROM ''LDAP://dc-1''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'''
> )
>
> --
> Regards
> Steen Schlüter Persson
> DBA
>

Help Setting Up A Linked Server to Query Directory Services

I'm not sure what I'm doing wrong here but I've been banging my head against
for a few hours now.
Background:
SQL 2000 on Windows 2000 Server SP4 as member server in an Active Directory
Domain
MSSQLSVR service and SQL Agent are running as a Domain Administrator
Ive tried to create a linked server with "OLE DB Provider for Microsoft
Directory Services" as the data source. Ive tried this via Query Analylzer
and via Enterprise Manager. The result is the same:
select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
where objectCategory="person" and objectClass="users" ')
Gives this error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e14].
I've tried to create it this way via QA:
sp_addlinkedserver 'AD', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
and then in EM, Ive played with all the security stuff but it's still a no
go. I'm completely at my limit with this. Can anyone offer a suggestion?
Thanks in advance.
Errol NealErrol Neal wrote:
> I'm not sure what I'm doing wrong here but I've been banging my head again
st
> for a few hours now.
> Background:
> SQL 2000 on Windows 2000 Server SP4 as member server in an Active Director
y
> Domain
> MSSQLSVR service and SQL Agent are running as a Domain Administrator
> Ive tried to create a linked server with "OLE DB Provider for Microsoft
> Directory Services" as the data source. Ive tried this via Query Analylzer
> and via Enterprise Manager. The result is the same:
> select cn from openquery(AD,'select cn from "LDAP://DC=dfi-intl,dc=com"
> where objectCategory="person" and objectClass="users" ')
> Gives this error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Pr
epare
> returned 0x80040e14].
> I've tried to create it this way via QA:
> sp_addlinkedserver 'AD', 'Active Directory Service
> Interfaces', 'ADSDSOObject', 'adsdatasource'
> and then in EM, Ive played with all the security stuff but it's still a no
> go. I'm completely at my limit with this. Can anyone offer a suggestion?
> Thanks in advance.
> Errol Neal
Hi
I don't know if it helps, but try to alter your OPENQUERY statement a
little bit.
The following works for me and compared to yours I've only specified the
AD servername and not with DC= as you have.
select * from openquery
(
ADSI,'SELECT name, mail, cn
FROM ''LDAP://dc-1''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
Regards
Steen Schlüter Persson
DBA|||Thanks a lot! Syntax was defintely an issue. Using single quotes instead of
a
double quote. I appreciate the reply!
"Steen Persson (DK)" wrote:

> Errol Neal wrote:
> Hi
> I don't know if it helps, but try to alter your OPENQUERY statement a
> little bit.
> The following works for me and compared to yours I've only specified the
> AD servername and not with DC= as you have.
> select * from openquery
> (
> ADSI,'SELECT name, mail, cn
> FROM ''LDAP://dc-1''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'''
> )
>
> --
> Regards
> Steen Schlüter Persson
> DBA
>