Best way to do SELECT statement

[johnc83]

Hi all, once again I am faced with a select statement that, although I could probably work it out myself, chances are that it will not be the most efficient way to do it..

Here is an example table to show what I am talking about:

ProductCode, ProductName, LinkProduct1, LinkProduct2
001, Bread, 002, 004
002, Butter, 003, NULL
003, Knife, NULL, NULL
004, Milk, NULL, NULL

If my user selected the ProductCode '001' the output datatable would look like this(Doesn't have to be in this order):

ProductCode, ProductName,
001, Bread
002, Butter
003, Knife
004, Milk

This is because they chose their main product (Bread) but whenever you buy bread the linked method dictates that you must also get Butter and Milk. Now because Butter was selected, it must also show Knife because that is Butters linked method.

My actual table has up to 6 possible linked Products.

I'm not lazy so I am not looking for the exact answer, I would just like pointing in the right direction please..

Thanks in advance for any replies.

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[philhege]

First of all, someone at some point in the future will come up to you and say, "John, we really need a seventh linked product."

Here's where you start thinking about data design. A "link" is really a relationship. So if you have one table that describes products, and another table that holds the relationships among the products, the design becomes extensible and easier to manage.

What you end up with is a schema that lends itself well to a recursive function, one that digs through the relationships until they're all enumerated.

There are a few threads in this Forum about recursion, like this one, that should give you some good ideas.

Have fun!



Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[Qik3Coder]

Going to start off with E

http://WWWWW.

oki, I'm all better now.

You are going to have to figure out how deep you want to go on this. You will have to force link each time.
It's easy enough to take the following:

ProductCode, ProductName, LinkProduct1, LinkProduct2
001, Bread, 002, 004
002, Butter, 003, NULL
003, Knife, NULL, NULL
004, Milk, NULL, NULL

Select t1.* from t1 join t2 on
t2.ProductCode in (t1.LinkProd1, t1.LinkProd2,...)

but to go from t1 to t2 to t3 is a little more. I've had to do it myself, and you're right, it's not pretty. So you got ask yourself "Do you feel lucky", err, how deep do you want to go.


You could build a temp table and load it like this:
you could probably do some dynamic SQL with a loop to run through the 6 link cols.
insert in temp table
Select t1.prod_code, t1.LinkCode1 from t1
where LinkCode1 is not null

insert in temp table
Select t1.prod_code, t1.LinkCode2 from t1
where LinkCode2 is not null

Then delete any dupes

Now find any relations (dynamic sql loop here...)
insert into temp table
select temp1.ProdCode, t1.LinkProd1 from tempTable join t1 on tempTable.LinkProdCode = t1.prodCode

Then actually hit the real table, remembering to do your vertical rows into a horizontal column.

Voila...and eww...

David

-Sometimes the answer to your question is the hack that works

 

[r937]

this is a great example of the mess that the sql can end up in when first normal form is violated

john, you might not know what first normal form is, so that would be worth a google

if it turns out that you have no control over the design of the table, it sometimes helps if you can track down the person who did design it, and give him or her a right swift whack on the side of the head

;-)

r937.com | rudy.ca

 

[Qik3Coder]

I agree with r937 on this.

If you have the ability to separate the data out, like you will wind up doing with the link loading anyways then you can at leasy make your life a lot easier.

-Sometimes the answer to your question is the hack that works

 

[johnc83]

Hi all, thanks for the responses...

I actually am familiar with normal form and I understand your points.

Main reason I avoided doing the related tables route is because I wanted to ensure that the speed of this one particular query was a fast as possible and I thought (maybe naively) that having all the links in the one table would ensure this.

I guess I should go with the 2 tables first and see if there are any speed problems then before dismissing it?

OK so if this was my tblMethods

ProductCode, ProductName
001,         Bread
002,         Butter
003,         Knife
004,         Milk

and this was my tblLinkedMethods

ProductCode, LinkedMethod
001,         002
001,         004
002,         003

how would I go about writing the select statement to get my desired output?

Thanks again for your replies, really appreciate the time taken by everyone.

Thanks

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[r937]

the select statement would involve a series of LEFT OUTER JOINs

FROM methods AS M
LEFT OUTER
JOIN links AS L1
LEFT OUTER
JOIN methods as M1
LEFT OUTER
JOIN links AS L2
LEFT OUTER
JOIN methods as M2
...

as Qik3Coder says, "you are going to have to figure out how deep you want to go on this"



r937.com | rudy.ca

 

[johnc83]

Thank you. That should be enough to get me started.

Cheers

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[bborissov]

Check this (SQL Server 2005 only!)

DECLARE @Test TABLE (ProductCode char(3), ProductName varchar(30))
INSERT INTO @Test VALUES('001','Bread')
INSERT INTO @Test VALUES('002','Butter')
INSERT INTO @Test VALUES('003','Knife')
INSERT INTO @Test VALUES('004','Milk')
INSERT INTO @Test VALUES('005','Milk 2')
INSERT INTO @Test VALUES('006','Milk 3')
INSERT INTO @Test VALUES('007','Milk 4')


DECLARE @Test1 TABLE (ProductCode char(3), LinkedMethod char(3))
INSERT INTO @Test1 VALUES('001','002')
INSERT INTO @Test1 VALUES('001','004')
INSERT INTO @Test1 VALUES('002','003')
INSERT INTO @Test1 VALUES('005','001')
INSERT INTO @Test1 VALUES('002','006')
INSERT INTO @Test1 VALUES('007','001')

DECLARE @Code char(3)
SET @Code = '001'
;
WITH TestMe (ProductCode, ProductName) AS
(
      SELECT ProductCode,ProductName
             FROM @Test
      WHERE ProductCode = @Code
      UNION ALL
      SELECT Test.ProductCode,Test.ProductName
             FROM @Test Test
      INNER JOIN @Test1 Test1 ON Test1.LinkedMethod = Test.ProductCode
 )

SELECT DISTINCT * FROM TestMe

I'm not a master of recursive queries and I hope if someone will improve it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP