Unusual join, need help! - 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 2005 Forums

Transact-SQL (2005)

Unusual join, need help!

Author

Topic

mtl777
Yak Posting Veteran

63 Posts

Posted - 2011-04-13 : 16:06:56

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 - 2011-04-13 : 16:08:58

Sorry that the columns do not line up! How do I make them line up?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-13 : 16:57:22

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

mtl777
Yak Posting Veteran

63 Posts

Posted - 2011-04-13 : 18:16:10

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!

mtl777
Yak Posting Veteran

63 Posts

Posted - 2011-04-13 : 20:01:10

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.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-13 : 20:59:46

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

mtl777
Yak Posting Veteran

63 Posts

Posted - 2011-04-14 : 12:07:16

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!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-14 : 12:44:27

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources