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.
Author |
Topic |
pituti
Starting Member
6 Posts |
Posted - 2006-07-06 : 10:42:40
|
This is my situation, I have a table Year1 (Y1), anotherone with the same structure Year2 (Y2)and the main one with the articles called S. In Y1 and Y2 I have the units of the articles in two diferent years, organized by weeks.I want to join S with Y1 and Y2 through the key code that all of them have, and filtering by the week of the year they have in Y1 and Y2.so, this is what I would like to get:example:s.article y1.article y1.units y2.article y2.units1234 1234 23 1234 3421235 NULL NULL 1234 524 (there was no units)but the ploblem is that I get only the units which had units in both of the Y, or at least in Y1... but not when there were units in Y2 and not in Y1...i don't know if I have explained correctly... :(SELECT DISTINCT S.article, S.[Plus Artikel_dt], ISNULL(a.cost, 0) AS costAct, ISNULL(b.cost, 0) AS costPas, ISNULL(a.cost, 0) - ISNULL(b.cost, 0) AS ImpDif, ISNULL(a.units, 0) AS UdsAct, ISNULL(b.units, 0) AS UdsPas, ISNULL(a.units, 0) - ISNULL(b.units, 0) AS UdsDifFROM dbo.Y2 b RIGHT OUTER JOIN dbo.Sortimentlisten S LEFT OUTER JOIN dbo.Y1 a ON S.article = a.article ON b.article = S.articleWHERE (b.week = [ & week & ] OR b.week IS NULL) AND (a.week = [ & week & ] OR a.week IS NULL)ORDER BY S.articlethanks!!!!! |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 10:58:49
|
May be this is happening due to the NULL values in the Article field of Y1 table. Rewrite the join condition to include IsNull() like below:SELECT DISTINCT S.article, S.[Plus Artikel_dt], ISNULL(a.cost, 0) AS costAct, ISNULL(b.cost, 0) AS costPas, ISNULL(a.cost, 0) - ISNULL(b.cost, 0) AS ImpDif, ISNULL(a.units, 0) AS UdsAct, ISNULL(b.units, 0) AS UdsPas, ISNULL(a.units, 0) - ISNULL(b.units, 0) AS UdsDifFROM dbo.Y2 b RIGHT OUTER JOINdbo.Sortimentlisten S LEFT OUTER JOINdbo.Y1 a ON IsNull(S.article,0) = IsNull(a.article,0) ON IsNull(b.article,0) = IsNull(S.article,0)WHERE (b.week = [ & week & ] ORb.week IS NULL) AND (a.week = [ & week & ] ORa.week IS NULL)ORDER BY S.articleand give it a try...Harsh AthalyeIndia."Nothing is Impossible" |
|
|
pituti
Starting Member
6 Posts |
Posted - 2006-07-06 : 11:25:19
|
many many many thanks Harsh Athalye,but the problem is when there where no units of one article in one week, there is no row, simply it was not registered, so i think I was wrong using the null, but I cannot get to show both tables when one of the them had no registers in one week.I tried also Y1.week=Y2.week, but that's also wrong, because I don't get all the registers.I think I'm getting nearer...thanks again! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 11:35:18
|
quote: Originally posted by pituti many many many thanks Harsh Athalye,but the problem is when there where no units of one article in one week, there is no row, simply it was not registered, so i think I was wrong using the null, but I cannot get to show both tables when one of the them had no registers in one week.I tried also Y1.week=Y2.week, but that's also wrong, because I don't get all the registers.I think I'm getting nearer...thanks again!
Why don't you try Full Outer Join instead of LEFT OUTER JOIN since there may be case when you have row in Y1 but not corresponding row in Y2 or vice versa!Harsh AthalyeIndia."Nothing is Impossible" |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-06 : 11:51:53
|
pituti - I find it difficult to understand things like this without examples (of starting structure / data and desired results), but I think something like this might be what you're after.......FROM dbo.Sortimentlisten S LEFT OUTER JOIN dbo.Y1 a ON S.article = a.article LEFT OUTER JOIN dbo.Y2 b ON S.article = b.articleWHERE.... Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
pituti
Starting Member
6 Posts |
Posted - 2006-07-06 : 11:52:17
|
thanks once more Harsh,I'm sorry if my question/problem sounds to easy or stupid, but I got blocked...Actually I've tried already with a full outer join, but I didn't get what was expected.I continue trying. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-06 : 12:07:26
|
If I transform your query like this:SELECT DISTINCT S.article, S.[Plus Artikel_dt], ISNULL(a.cost, 0) AS costAct, ISNULL(b.cost, 0) AS costPas, ISNULL(a.cost, 0) - ISNULL(b.cost, 0) AS ImpDif, ISNULL(a.units, 0) AS UdsAct, ISNULL(b.units, 0) AS UdsPas, ISNULL(a.units, 0) - ISNULL(b.units, 0) AS UdsDiffrom dbo.y1 as a Full Outer Join dbo.y2 on a.article = b.article Inner Join dbo.Sortimentlisten S on a.article = s.article and b.article = s.articleWHERE (b.week = [ & week & ] OR b.week IS NULL) AND (a.week = [ & week & ] OR a.week IS NULL)ORDER BY S.articleIs it the way you wanted?Because I think Full Outer Join can only provide correct solution to your requirement. Can u show me the expected output?Harsh AthalyeIndia."Nothing is Impossible" |
|
|
pituti
Starting Member
6 Posts |
Posted - 2006-07-06 : 12:20:59
|
thanks to all of your,i'm really impressed and happy about this forum, I've never seen people so generous sharing their knowledge.and about my question,the problem is I can not join so directly Y1 and Y2 trough the article, because there is the possibility one article is not in both of them (there will be one if in both years they sold that product), and I get also problems if I join them with the week, because there is the possibility if If the article was sold in both years, it was not in the same week...... i don't know if I have explained correctly,... probably it has not the best structure, but it is what i have received... |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-06 : 12:31:26
|
pituti - did you see my reply (above)? Would you please respond to it...If that doesn't give you what you need, post your ddl, some sample data, and the results you like for that sample data. It's so much easier to help you if you give us this information.Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
pituti
Starting Member
6 Posts |
Posted - 2006-07-06 : 12:53:13
|
sorry for not answering to your reply (i was answering the other one when you wrote it).I've tried also with that one, but it didn't work.STOOOOOP....I begun to copy the structure of my tables, when suddenly i discovered the problem (i think), i had the primary key in Y1 (with article and week) but not in Y2, this is why i was getting always only the registers of Y1...Always is the same, you need 'at least' to tell/show it to some one to see, what you were seeing continuesly :-)thanks again to all of you, and also to the forum, i you let me I'll share more time with you here, and hope to be also usefulhasta la vista!I don't know if I will be able to give you what you ask me for, i'll try (i'll copy the same structure as i have, i'm sorry, it is a mix of spanish and german, but it is understandable).' Y1CREATE TABLE [AnioActual] ( [articulo] [numeric](8, 0) NOT NULL , [unidades] [float] NULL , [importe] [float] NULL , [semana] [int] NOT NULL , [anio] [int] NULL , [Margen] [float] NULL , CONSTRAINT [PK_AnioActual] PRIMARY KEY CLUSTERED ( [articulo], [semana] ) ON [PRIMARY] ) ON [PRIMARY]GO |
|
|
|
|
|
|
|