SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cant get right result from my query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidoff
Starting Member

7 Posts

Posted - 04/09/2013 :  08:36:59  Show Profile  Reply with Quote
Hi!

I have multiple tables:

       order (id,quantity, status, notes, item_id, customer_id)
       item (id,number, name)
       order_items(order_id, item_id)
       customer(id, name)

I have simple program in which in main window I have orders and their statuses(done, pending) that I show on `datagridview`. If I double click on a row, I collect new variable which is basically `order_id` and then I open my order with items ordered in new `datagridview`. But I just can't get right result in second datagridview.

With code bellow I fill my first datagridview

Dim sql As String = "SELECT DISTINCT customer.name, order.status, order.notes, order_items.oder_id FROM ((order INNER JOIN order_items ON order.id = order.items_order_id) INNER JOIN customer ON order.customer_id = customer.id)"

When I double click on a row I get variable `order.id` as integer and then I use it in code bellow.

Dim sql As String = "SELECT item.number, item.name, order.status, order_notes FROM  ((order INNER JOIN order_items ON order.id = order_items.order_id) INNER JOIN  item ON order_items.item_id = item.id) WHERE (order_items.order_id = " & cellcliked.value & ")"

The items are showing fine (number, name) but the order(status, notes) is the same as first item on my order. For every item that follows on that order. Just cant' get it.

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/09/2013 :  12:14:55  Show Profile  Reply with Quote
If I'm reading that correctly, then that is expected behavior. You have multiple Items per Order. the Order, presumably, only has one Status and Note. So you will get the same Status and Note for each item associated with that order. If that is an incorrect reading of what your posted above, please post sample data in a consumable format and expected outpout and we can help you better.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000