Help with join in query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with join in query?

Author

Topic

jenn5175
Starting Member

1 Post

Posted - 2007-05-04 : 12:02:34

I am not sure this is possible but before go down the route of lots of loops, figured it wouldn't hurt to ask.

I have 1 products table - in that table each product has iProductID (PK), sModel (manufacturer's model), sSellingFeatures (brief desc of prod such as "perfect for home or office"), and sProdDec (more detailed description of product).

We just got a new supplier who is feeding us a few thousand products updated every day. Because we now have over 4000 products we can no longer go in by hand and associate products together (for example, we sell projectors and projector accessories and I have a table that associates two iProductIDs together so when someone views a projector, they see the remote, lens, replacement lamp, etc).

I would like to automate a way to associate products together. However, the only way you know what goes with what is the projector model the accessory is for is hidden somewhere in the selling features or description. So some example records might be:

iProductID, sModel, sSellingFeatures, sProdDesc
1, LT35, cool projector, blah blah blah about this projector
2, xyz123, replacement lamp for LT35, this lamp fits the LT35 projector blah blah blah
3, xyz000, lens for projector LT35, here is the lens for the LT35 projector

Ultimately I would like to get to 1 update statement that would add 1,2 and 1,3 into my accessories table. However, right now I am just trying to form a select statement that will join together products with other products where their model appears in the sellingfeatures or product desc.

Here is what I have so far:


SELECT tProdMain.iProductID, tProdMain.sModel, tProdAcc.iProductID AS Expr1, tProdAcc.sModel AS Expr2, tProdAcc.sProdDesc
FROM tProducts tProdMain INNER JOIN
tProducts tProdAcc ON tProdAcc.sProdDesc LIKE '%tProdMain.sModel%' OR tProdAcc.sSellingFeatures LIKE '%tProdMain.sModel%'

It doesn't appear I can use "LIKE" in the ON portion of a JOIN. Any ideas on how to grab the recordset I am looking for?

Thanks!
Jenn

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 12:06:17

[code]ON tProdAcc.sProdDesc LIKE '%' + tProdMain.sModel + '%' OR tProdAcc.sSellingFeatures LIKE '%' + tProdMain.sModel + '%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources