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
 Old Forums
 CLOSED - General SQL Server
 When I join two tables, I get only one ...

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.units

1234 1234 23 1234 342
1235 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 UdsDif
FROM dbo.Y2 b RIGHT OUTER JOIN
dbo.Sortimentlisten S LEFT OUTER JOIN
dbo.Y1 a ON S.article = a.article ON b.article = S.article
WHERE (b.week = [ & week & ] OR
b.week IS NULL) AND (a.week = [ & week & ] OR
a.week IS NULL)
ORDER BY S.article


thanks!!!!!





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 UdsDif
FROM dbo.Y2 b RIGHT OUTER JOIN
dbo.Sortimentlisten S LEFT OUTER JOIN
dbo.Y1 a ON IsNull(S.article,0) = IsNull(a.article,0) ON IsNull(b.article,0) = IsNull(S.article,0)
WHERE (b.week = [ & week & ] OR
b.week IS NULL) AND (a.week = [ & week & ] OR
a.week IS NULL)
ORDER BY S.article


and give it a try...



Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

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!

Go to Top of Page

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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page

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.article
WHERE
....


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

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.

Go to Top of Page

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 UdsDif
from 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.article
WHERE (b.week = [ & week & ] OR b.week IS NULL) AND (a.week = [ & week & ] OR a.week IS NULL)
ORDER BY S.article

Is 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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page

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...
Go to Top of Page

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 Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

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 useful

hasta 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).

' Y1
CREATE 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

Go to Top of Page
   

- Advertisement -