SQL View - 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

General SQL Server Forums

New to SQL Server Programming

SQL View

Author

Topic

matthew
Starting Member

2 Posts

Posted - 2010-06-07 : 04:05:03

Hi,
Have a huge problem with what seems to be a simple query. Here it is: Imagine a simple table that contains products (1,2,3,.. ) and models that those products are asoc with (AA, AB, AC, ..):

model, product
AA 1
AA 2
AB 1
AB 2
AB 3
AC 1
AC 3
AD 4
AD 5
AD 6
AE 1
AE 7

Now we want to find out which products are related to product 1:
easy - 2, 3, 7.

The task is to create a view where those relations are shown as follows:

VIEW related:
product, related
1,2
1,3
1,7
2,1
2,3
3,1
3,2
4,5
4,6
...

Any one knows how to create such a view? I have tried thousends times and no idea.

Thx,
matthew

PS. For the purpose of task we can assume that another table of PRODUCTS exists and not all products have model associations.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-07 : 04:44:57

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Model CHAR(2) NOT NULL,
ProductID TINYINT NOT NULL
)

-- Populate sample data
INSERT @Sample
(
Model,
ProductID
)
VALUES ('AA', 1),
('AA', 2),
('AB', 1),
('AB', 2),
('AB', 3),
('AC', 1),
('AC', 3),
('AD', 4),
('AD', 5),
('AD', 6),
('AE', 1),
('AE', 7)

-- Display the expected output (this is also the view definition)
SELECT src.ProductID AS Product,
tgt.ProductID AS Related
FROM @Sample AS src
INNER JOIN @Sample AS tgt ON tgt.Model = src.Model
WHERE src.ProductID <> tgt.ProductID
GROUP BY src.ProductID,
tgt.ProductID[/code]

N 56°04'39.26"
E 12°55'05.63"

matthew
Starting Member

2 Posts

Posted - 2010-06-07 : 05:46:07

Thank you, it works great! I have spent 8 hours and couldnt get this done. Now it's fine. Once again thanks.

Sachin.Nand

2937 Posts

Posted - 2010-06-07 : 06:01:26


	select distinct s1.ProductID,S.ProductID as Related from @Sample s1
		cross apply(select top 100 percent with ties * from @Sample s2 
		where s1.Model=s2.Model and s1.ProductID<>s2.ProductID order by ProductID)S
		order by s1.ProductID

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources