joining a table with itself and with another table

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 2000 Forums

Transact-SQL (2000)

joining a table with itself and with another table

Author

Topic

cyrus71
Starting Member

2 Posts

Posted - 2006-11-02 : 15:04:56

table Book is.

bookid ................ int ................. 1, 2, 3,....
group................ string ............... A, B , C, D some posts have same group
Pay .................. decimal.............
Leas .................decimal ............
type ................. string ............... either P or L, when it is P Pay is not null and Leas is null, when it is L Pay is null and Leas is not Null

table V is:

vid ................... int .................... 1,2, 3,....
bookId ................int .................... 1, 2, 3, ...
price ................decimal .............

when i run following stored procedure it generates syntax error (@group is input variable)

SELECT SUM(V.Price - b1.Pay) + SUM(V.price - b2.Leas) AS CreditAmount

FROM book b1, book b2 INNER JOIN V ON b1.bookId = V.bookId AND b2.bookId = V.bookId

WHERE V.Price <> 0 AND b1.gruop = @gruop AND b2.gruop = @gruop AND
b1.Pay = CASE b1.Pay WHEN NULL THEN 0 ELSE b1.Pay END

AND b2.Leas = CASE b2.Leas WHEN NULL THEN 0 ELSE b2.Leas END

(when leas or Pay is null i use 0 because i dont want to miss a post)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-02 : 19:26:18

Your query doesn't make sense.

What will the output look like? What is the credit amount supposed to be? The way you have it will make the CreditAmount equal to 2 times the V price (less Pay and or Leas).

You will have to give some example data (not example column values but actual rows) and also some examples of what the corresponding output from your query need to be if you want help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-04 : 06:35:58

What is the error you got? As said post some sample data and the result you want. Also show us how you call procedure by passing @group value

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-04 : 22:27:26

[code]
SELECT SUM(V.Price - b1.Pay) + SUM(V.price - b2.Leas) AS CreditAmount
FROM book b1
INNER JOIN book b2 ON b1.bookId = b2.bookId
INNER JOIN V ON b1.bookId = V.bookId
WHERE V.Price <> 0
AND b1.gruop = @gruop
-- is this necessary if bookid is the primary key then this is redundant
~~AND b2.gruop = @gruop~~
-- what is this for ?
AND b1.Pay = CASE b1.Pay WHEN NULL THEN 0 ELSE b1.Pay END
AND b2.Leas = CASE b2.Leas WHEN NULL THEN 0 ELSE b2.Leas END
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-04 : 22:33:33

Infact, why do you need to reference the BOOK table twice ?
if bookid is the primary key for BOOK, This "b1.bookId = V.bookId AND b2.bookId = V.bookId" will means "b1.bookid = b2.bookid". So you are referencing the same record of table BOOK. Which means you don't have to create b1 and b2 to reference table BOOK. And the query can be simplified to


SELECT 	   SUM(V.Price - b.Pay) + SUM(V.price - b.Leas) AS CreditAmount
FROM 	   book b 	
INNER JOIN V 		ON b.bookId = V.bookId
WHERE 	   V.Price <> 0 
AND 	   b.gruop = @gruop

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources