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  Development Tools  ASP.NET  Display SQL data from 2 different tables

Author  Topic  tate-u Starting Member 1 Post Posted - 2009-09-10 : 12:20:26 Can someone help and show me how to display using these two tables combined into one using vb.net. I'm really struggling trying to figure this out after hours of work, I decided its time to ask for help, and have never had this much trouble with queries or any code for that matter! So in SQL Server 2005 I have the following tables... Students PKId Cart_Id Fname Lname Credits (will not always equal one) Items_Ordered PkId Cart_Id Product_Id Quantity (will always be one) Essentially, for the Items_Ordered table there could be two that look like 1001 897778765 S5532 1 1001 897778765 S5532 1 Meaning there could be a quantity of two or more for an item, but they each use their own line. Example of rows in Students table with corresponding Cart_Id ---------------------------------------------------------- PKId | Cart_Id | Fname | Lname | Credits | ---------------------------------------------------------- 758 | 983772837 | Jon | Smith | 4 | 759 | 983772837 | Jane | Smith | 4 | 760 | 983772837 | Jimmy | Smith | 4 | 761 | 983772837 | Judy | Smith | 3 | ---------------------------------------------------------- Example of rows in Items_Ordered table with corresponding products purchased --------------------------------------------------- PKId | Cart_Id | Product_Id | Quantity | --------------------------------------------------- 758 | 983772837 | 2344 | 1 | 759 | 983772837 | 2344 | 1 | 760 | 983772837 | 2344 | 1 | 761 | 983772837 | 4566 | 1 | 761 | 983772837 | 4566 | 1 | 761 | 983772837 | 3442 | 1 | 761 | 983772837 | 8876 | 1 | 761 | 983772837 | 9099 | 1 | 761 | 983772837 | 9099 | 1 | 761 | 983772837 | 9099 | 1 | 761 | 983772837 | 9099 | 1 | 761 | 983772837 | 9099 | 1 | 761 | 983772837 | 1332 | 1 | 761 | 983772837 | 7465 | 1 | 761 | 983772837 | 7465 | 1 | --------------------------------------------------- The following is what has to happen... The total amount of credits should always equal the total quantity of items ordered for that Cart_Id. Now, the amount of Students there are for each purchase will vary from 1 - 4 and the credits need to be distributed evenly. If there is an uneven Quantity versus Student count, then the credits will be allocated from top to bottom, adding an extra to each of those students. So when it comes down to it, I need Students.Fname and Students.Lname put into each line of the Items_Ordered table for that Cart_id, distributed evenly. I would really appreciate any help on this, as I am almost brain dead now. Thanks!