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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Unusual join, need help!
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mtl777
Yak Posting Veteran

63 Posts

Posted - 04/13/2011 :  16:06:56  Show Profile  Reply with Quote
Hi, I have an unusual kind of join or query that I'm trying to figure out how to do without using a cursor. Let's say there are two tables, Table1 and Table2 as shown below. I would like them to match up by ProdCode and join them together such that the matching sets of rows are side by side, with the left side which is from Table1 sorted by T1ID and the right side which is from Table2 sorted by T2ID (please see the Join below).

Note that in the third row of the Join, the right side (fields from Table2) has all NULLs because there are only two rows in Table2 that have a ProdCode = 'MIC-01' whereas there are three rows in Table1 that have a ProdCode = 'MIC-01'. Thus, the third 'MIC-01' in Table1 does not have a corresponding third 'MIC-01' to match up with in Table2.

I would really appreciate any help on this. Thanks in advance!


Table1:

T1ID ProdCode ProdName BuyDate
6 PRE-03 Neve-1073 03/03/11
3 PRE-03 Neve-1073 03/01/11
4 PRE-03 Neve-1073 03/02/11
1 MIC-01 Neum-U47 04/03/11
5 MIC-01 Neum-U47 04/07/11
2 MIC-01 Neum-U47 04/05/11

Table2:

T2ID ProdCode ProdName SellDate
9 PRE-03 Neve-1073 03/06/11
6 PRE-03 Neve-1073 03/04/11
7 PRE-03 Neve-1073 03/05/11
8 MIC-01 Neum-U47 04/10/11
4 MIC-01 Neum-U47 04/06/11

Join:

-------Fields from Table1------- -------Fields from Table2-------
T1ID ProdCode ProdName BuyDate T2ID ProdCode ProdName SellDate
1 MIC-01 Neum-U47 04/03/11 4 MIC-01 Neum-U47 04/06/11
2 MIC-01 Neum-U47 04/05/11 8 MIC-01 Neum-U47 04/10/11
5 MIC-01 Neum-U47 04/07/11 NULL NULL NULL NULL
3 PRE-03 Neve-1073 03/01/11 6 PRE-03 Neve-1073 03/04/11
4 PRE-03 Neve-1073 03/02/11 7 PRE-03 Neve-1073 03/05/11
6 PRE-03 Neve-1073 03/03/11 9 PRE-03 Neve-1073 03/06/11

mtl777
Yak Posting Veteran

63 Posts

Posted - 04/13/2011 :  16:08:58  Show Profile  Reply with Quote
Sorry that the columns do not line up! How do I make them line up?
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
819 Posts

Posted - 04/13/2011 :  16:57:22  Show Profile  Reply with Quote
It looks to me like the tables are related using ProdCode. If that is so, then this should work:


SELECT *
  FROM dbo.table1 t1
  LEFT JOIN dbo.table2 t2 ON t2.ProdCode = t1.ProdCode


However, this will not work because you don't have anything else to limit the relationship. You need to be able to identify which row is related from each table. For example, how is the row T1ID = 1 related to T2ID = 4? If you are just looking at the order of the rows - that really doesn't work but you could do the following:


  ;WITH t1 (
SELECT *
       ,row_number() over(PARTITION BY ProdCode ORDER BY BuyDate) AS rownum
  FROM table1
), t2 (
SELECT *
       ,row_number() over(PARTITION BY ProdCode ORDER BY SellDate) AS rownum
  FROM table2
)
SELECT *
  FROM t1
  LEFT JOIN t2 ON t2.ProdCode = t1.ProdCode
              AND t2.rownum = t1.rownum


Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 04/13/2011 :  18:16:10  Show Profile  Reply with Quote
Thanks for replying!

Yes, I'm just looking at the order of the rows, sorted by the ID (T1ID or T2ID). So I did the following query:


WITH t1 (
SELECT *
,row_number() over(PARTITION BY ProdCode ORDER BY T1ID) AS rownum
FROM table1
), t2 (
SELECT *
,row_number() over(PARTITION BY ProdCode ORDER BY T2ID) AS rownum
FROM table2
)
SELECT *
FROM t1
LEFT JOIN t2 ON t2.ProdCode = t1.ProdCode
AND t2.rownum = t1.rownum


But it gives me the following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Any idea what's wrong? Thanks again!
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 04/13/2011 :  20:01:10  Show Profile  Reply with Quote
OK, I tried it this way and it works now:


WITH t1 AS (
SELECT *
,row_number() over(PARTITION BY ProdCode ORDER BY T1ID) AS rownum
FROM table1
), t2 AS (
SELECT *
,row_number() over(PARTITION BY ProdCode ORDER BY T2ID) AS rownum
FROM table2
)
SELECT *
FROM t1
LEFT JOIN t2 ON t2.ProdCode = t1.ProdCode
AND t2.rownum = t1.rownum


It was just missing the 'AS'. Wow, this is so awesome and powerful stuff! It's unbelievably simple! I didn't know you could this until now. I have learned something new. Thank you so much!!! I really appreciate your help. This site is great with very helpful people like you! I'll be coming back here whenever I have a SQL problem.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
819 Posts

Posted - 04/13/2011 :  20:59:46  Show Profile  Reply with Quote
You are welcome - and sorry for the typo.

Just make sure you understand that there is no relationship between the two. It could very well be that T1ID of 1 is actually related to T2ID of 8 and not 4. There really should be a way to identify the sell based upon the buy - but then again, I am not sure what this is actually for.

Jeff
Go to Top of Page

mtl777
Yak Posting Veteran

63 Posts

Posted - 04/14/2011 :  12:07:16  Show Profile  Reply with Quote
It's just an oversimplification of a way more complex problem, for ease of posting the question in a forum. I just needed the basic query and principle involved.

Now there's a problem: The test query works in SQL Server 2005, but doing it with the actual application which is in SQL Server 2000 doesn't work. I realize that Row_Number() is not available in 2000. Is there an equivalent way to do this in 2000? I don't mind if I have to create temp tables. Thanks!
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 04/14/2011 :  12:44:27  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
On mssql server 2000 return two data sets to a client and let the client make a "join". That's better then any pure sql solution.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000