July 10, 2012 at 1:53 pm
Hello,
We are working with a table that has a list of ~400 uniquely identified products. I need to impliment some type of join so that I can pair one product with the other 399, and do this for each other products, creating pairs. It makes sense to create an identical table with a new set of primary keys, but I'd rather do this with some type of join.
Example:
Table_Product
Product 1
Product 2
Product 3
Product 1- Product 2
Product 1- Product 3
Product 2- Product 1
Product 2- Product 3
Product 3- Product 1
Product 3- Product 2
I am very new with SQL, please feel free to ask any questions that will help clarify my question. All input welcome!
Thanks.
July 10, 2012 at 2:02 pm
a cross join should accomplish what you want(http://msdn.microsoft.com/en-us/library/ms190690%28v=sql.105%29.aspx)
here is an example
CREATE TABLE #foo(id INT)
INSERT INTO #foo(id)
VALUES(1),(2),(3)
SELECT a.id,b.id
FROM #foo a
CROSS JOIN foo b
WHERE a.id <> b.id
DROP TABLE #foo
July 10, 2012 at 2:04 pm
Have you tried a cross join on the same table?
SELECT a.product,
b.product
FROM ProductTable AS a
CROSS JOIN ProductTable AS b
WHERE a.product <> b.product
That will give you a cartesian product.
Edit: I guess I'm a slow replier.
July 10, 2012 at 2:22 pm
Hey Bob,
Thanks so much for your help; just to clarify, is the first step is to create a new table with the same records but a new primary? Is this done using the CreateTable function? Secondly, what is foo?
Thanks.
July 10, 2012 at 2:50 pm
cjcolton (7/10/2012)
Hey Bob,Thanks so much for your help; just to clarify, is the first step is to create a new table with the same records but a new primary? Is this done using the CreateTable function? Secondly, what is foo?
Thanks.
No you don't need another copy of your table. That is the point. A cross join creates a cartesian product which is what you are after.
There is no such thing as a CreateTable function.
foo is a made up table name because you didn't provide any ddl or even a table name to work with. It was a made up example.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2012 at 3:01 pm
Thanks very much; I will work on this and if I make any progress Ill let you know.
take care
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply