fun sql exercise!~ - 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)

fun sql exercise!~

Author

Topic

orangefan08
Starting Member

10 Posts

Posted - 2010-08-27 : 14:31:55

I have 3 tables like this...

table A

Item# Piece#
1 1
1 2
2 1
2 2

table B

Item# Piece#
1 1
2 1

table c

Item# Piece# Amt
1 1 5
1 2 10
1 3 15
2 1 5
2 2 10

The code is setup with a left join on table A.

The resulting table I want is

Item# Piece# Amt Amt2
1 1 5 15
1 2 10 null
2 1 5 null
2 2 10 null

So in table A has all the ITem# and Piece# I want to appear in the query, however table c has Piece# not contained then table A, but I want to see these Amts. Table b has the Item#, Piece# I want this Amt now to show up under.

Let me know what I need to clarify and thanks in advance!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-08-27 : 15:06:49

This would be a overall bad design if this was a live database scenerio, but I think you want
this


select a.*, case when not b.[item#] is null (Select sum(aa.Amt) from tablec aa left join tablea bb on aa.[item#] = bb.[item#]
and aa.[piece#] = bb.[piece#]
where aa.[Item#] = a.[Item#]
and bb.[Piece#] is null
)end as amt2
from tablea a
Left join
Tableb b
on a.[item#] = b.[item#]
and a.[piece#] = b.[piece#]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

orangefan08
Starting Member

10 Posts

Posted - 2010-08-27 : 15:36:02

Thanks Vinnie for the response.

I tried your code and it runs but it's not working properly as the totals don't match what they should before the joins. The number is too large so some records are being double counted I believe....

orangefan08
Starting Member

10 Posts

Posted - 2010-08-27 : 15:43:26

Nevermind, had an improper table join somewhere else. Thanks!

orangefan08
Starting Member

10 Posts

Posted - 2010-08-31 : 08:08:48

This is double counting values....here is a result set from Vinnie's code....

Here is TableA, this contains all the Item#/Piece# combinations I want.

Item# Piece#
1 1
1 4

Here is TableB, this contains all the Item#/Piece# as TableA but also contains additional Piece#'s for certain Item#'s.

Item# Piece# Amt
1 1 5
1 2 10
1 3 15
1 4 20

For these additional records not in TableA I want them to show up as a new column. A third table, TableC, contains the Item# and Piece# I want these Amts to associated with.

Hopefully you can see what I mean by the tables....

TableC

Item# Piece#
1 1

and so I would want this result set

Item# Piece# Amt Amt2
1 1 5 25
1 4 20

and unfortunately I'm getting this....

Item# Piece# Amt Amt2
1 1 5 45
1 4 20

So Piece#4 is being counted in Amt2 when I do not want it there.

Please let me know what additional info you need!

Thanks in advance!

orangefan08
Starting Member

10 Posts

Posted - 2010-08-31 : 08:25:55

NM, I have found a solution myself....

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-09-01 : 03:44:09

please can you share it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources