minimize database calls - 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

SQL Server Development (2000)

minimize database calls

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-28 : 03:05:44

Hi

I have 2 tables that looks like this...

Table 1

CartID NodeID Price Info

1 30 55 Shoe
2 41 34 Shirt

Table 2

ID CartID Company Adress City
1 1 Test company street 1 Largeville
2 1 Another company street 2 smallville
3 2 Test company street 1 Largeville

I then need to loop through all records in table i to display what have been ordered, but as you can see there can also be several adresses to each record in table 2. But I don't want to make another database call for each record in table 1 to get all associated adresses in table 2. Can this be done in one single stored procedure instead. Maybe by building up some kind of adress string that I later can process in my end page (.aspx).

Best Regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 03:09:52

Somthing like this


Select tbl2.Company,Tbl1.Price,Tbl1.Info From Table1 tbl1 Inner Join 
Table2 tbl2 on tbl1.CartID = tbl2.CartID

Chirag

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-28 : 03:53:47

Hi

This is what I use so far...

SELECT dbo.tbl_CartAdresses.Company, dbo.tbl_ShoppingCart.CartID, dbo.tbl_CartAdresses.Street, dbo.tbl_CartAdresses.PostalCode,
dbo.tbl_CartAdresses.City, dbo.tbl_ShoppingCart.UserID, dbo.tbl_ShoppingCart.NodeID, dbo.tbl_Products.Text
FROM dbo.tbl_CartAdresses INNER JOIN
dbo.tbl_ShoppingCart ON dbo.tbl_CartAdresses.CartID = dbo.tbl_ShoppingCart.CartID INNER JOIN
dbo.tbl_Products ON dbo.tbl_ShoppingCart.NodeID = dbo.tbl_Products.NodeId
WHERE (dbo.tbl_ShoppingCart.UserID = N'796-7922-1')

This give me all the records and their associated adresses, but the problem is that if I have one product in tbl_ShoppingCart associated with 2 or more adresses in tbl_CartAdresses, then duplicate products are shown. This is perfecly logical. But is there a way to not get duplicates, and instead retrieve all associated adresses in a single entry?

Regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 04:06:33

somthing like this


SELECT dbo.tbl_CartAdresses.Company, dbo.tbl_ShoppingCart.CartID, dbo.tbl_CartAdresses.Street, dbo.tbl_CartAdresses.PostalCode,
dbo.tbl_CartAdresses.City, dbo.tbl_ShoppingCart.UserID, dbo.tbl_ShoppingCart.NodeID, dbo.tbl_Products.Text
FROM dbo.tbl_CartAdresses INNER JOIN
dbo.tbl_ShoppingCart ON dbo.tbl_CartAdresses.CartID = dbo.tbl_ShoppingCart.CartID INNER JOIN
dbo.tbl_Products ON dbo.tbl_ShoppingCart.NodeID = dbo.tbl_Products.NodeId
WHERE (dbo.tbl_ShoppingCart.UserID = N'796-7922-1')
And dbo.tbl_ShoppingCart.CartID = ( Select Max(CartID) From 
dbo.tbl_CartAdresses a Where a.[ID]= dbo.tbl_CartAdresses.[ID])

Chirag

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-28 : 04:26:51

Hi

Hmmm, that give me the same result as the code I posted...

/M

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-28 : 04:40:11

what is the expected result ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 04:40:23

oh gosh..


SELECT dbo.tbl_CartAdresses.Company, dbo.tbl_ShoppingCart.CartID, dbo.tbl_CartAdresses.Street, dbo.tbl_CartAdresses.PostalCode,
dbo.tbl_CartAdresses.City, dbo.tbl_ShoppingCart.UserID, dbo.tbl_ShoppingCart.NodeID, dbo.tbl_Products.Text
FROM dbo.tbl_CartAdresses INNER JOIN
dbo.tbl_ShoppingCart ON dbo.tbl_CartAdresses.CartID = dbo.tbl_ShoppingCart.CartID INNER JOIN
dbo.tbl_Products ON dbo.tbl_ShoppingCart.NodeID = dbo.tbl_Products.NodeId
WHERE (dbo.tbl_ShoppingCart.UserID = N'796-7922-1')
And dbo.tbl_CartAdresses.CartID = ( Select Max(CartID) From 
dbo.tbl_CartAdresses a Where a.[ID]= dbo.tbl_CartAdresses.[ID])

Chirag

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-08-28 : 05:52:33

Hi

The latest query also resulted in the same output. As you can see there are 2 adresses specified for the product "Shoe". What I would like is to display those 2 adresses in a single record. Something like this...

NodeID Price Info Adresses
1 30 Shoe "Test company
street 1
Largevile¤
Another company
street 2
smallville"

2 41 Shirt "Test company
street 1
Largevile"

The adresses are seperated by a ¤ sign and company name, street and city is on seperate lines (linefeed)

The 2 adresses is in between "" but only so that you can see what the adress should be. Sorry that I can't display this better.

Hope this make sence...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources