A query problem - 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 2000 Forums

Transact-SQL (2000)

A query problem

Author

Topic

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-06 : 01:20:23

I have a query problem. I have 3 tables from which i need to get data.The structure of the tables are :
Tbl_HO_Stock(SK_SKID_PK,SK_Name,SK_Description,SK_UnitPrice,SK_MTSID_FK,SK_Stock_InHand,SK_Stock_Sold,SK_RCID_FK,SK_RCSKID_FK)
Tbl_HO_StockHistory (SH_SHID_PK,SK_SKID_FK,SH_Received,SH_Date,SH_Details,SH_ReceiptNo,SH_RCID_FK,SH_RCSKID_FK)
Tbl_HO_StockOutwards
(SO_SOID_PK,SK_SKID_FK,SO_Sold,SO_Date,SO_ReceiptNo,SO_SalesAmount
SO_Discount)

I am getting multiple values when i write the below query.

select sk.SK_Name,sh.SH_Received,so.SO_Sold,sk.SK_UnitPrice, so.SO_SalesAmount from Tbl_HO_Stock sk, Tbl_HO_StockHistory sh, Tbl_HO_StockOutwards so

Can you suggest me another way of doing this or modifiying this to get the result.

Thankyou..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 01:22:26

if you table is one to many relationship, when you inner join the tables, you will get this.

What do you want ? Post some sample data and result that you want

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-06 : 02:00:58

SELECT
sk.SK_Name,
sh.SH_Received,
so.SO_Sold,
sk.SK_UnitPrice,
so.SO_SalesAmount

FROM Tbl_HO_Stock SK
INNER JOIN Tbl_HO_StockHistory SH on
SK.SK_SKID_PK = SH.SK_SKID_FK and
SK.SK_RCID_FK = SH.SH_RCID_FK and
SK.SK_RCSKID_FK = SH.SH_RCSKID_FK
INNER JOIN Tbl_HO_StockOutwards SO
SH.SK_SKID_FK = SO.SK_SKID_FK

Thanks
KK

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-06 : 02:17:58

Ya,, it is having one to many realtion.
Here i'm giving you the sample data:

in Stock table data is
(44,vcd,vcd desc,12.00,18,2,0,4,48)
(58,pens,Desc, 10.00, 27,0,0,4,57)

in Stock history table data is
(276,46,10,2005-12-14,details,123a,4,276)
(280,53,2,2006-04-04, details,123,4,279)

in StockOutwards table data is
(114,45,2,2005-12-14,abc2,30,0,4,114)
(117,54,2,2006-04-04,502,26,0,4,117)

now the output that i want is:
(1,vcd,2,0,12,0)=> (stock_id, stock_recieved,stock_sold,unit price,total_sales_amount)
(2,pens,2,1,2,10)

i get
stock_ id,unitprice from Stock table,
stock _recieved from StockHistory table,
stock_sold,stock_total_sales_amount from StockOutwards table.

bettyatolive
Starting Member

21 Posts

Posted - 2006-06-06 : 02:44:24

Thanq CSK,
The query you gave worked out but with little change in field names. Thanq once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources