Pulling info from 2 SQL tables and displaying it...i'm stumped.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Pulling info from 2 SQL tables and displaying it...i'm stumped.

  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unhappy Pulling info from 2 SQL tables and displaying it...i'm stumped.

    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!

  2. #2
    Join Date
    Sep 2009
    Posts
    3
    First Get the difference of the total of Credits and total of quantity using a Sql query. EG:select Sum(Quantity) - Sum(Credits) from Students s join i items_ordered on s.Cart_id=i.Cart_id. you can do that using

    then divide the difference by 4 and get the resulting positive number and remainder in to two variables. then using a loop add values to respective users accordingly. then update the database. you can use for loop for this implement poisitve number and remainder in two loops. need more help?

Similar Threads

  1. multiple tables, recordsets, and sql ?
    By cyman73 in forum VB Classic
    Replies: 2
    Last Post: 11-18-2005, 01:10 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center