2 Replies - 609 Views - Last Post: 10 October 2011 - 02:10 PM Rate Topic: -----

#1 bemis82  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 03-June 11

Help with sql query

Posted 10 October 2011 - 01:47 PM

Hey All, quick SQL related question, i have transaction table that has all of the bag and item related fields for my business. Each transaction can have multiple Bags and each Bag has it's own skew number. Where it gets a little tricky is that we re-use the bags so over time they can show up in many transactions. Now what i'm trying to do is to search for all of the records in all of the transactions that a particular bag was in. However if I just run they query normally I will get all of the transactions that a bag was in, but not all of the records of all of the transactions that that particular bag was in because there were other records in the transaction that had different stuff in it. Below is what i have so far but it doesnt work because it errors out if there is more then 1 transaction number in the sub query. I was thinking of making a temporary table with all of the transaction numbers a bag would bring up and then run a select query joining it by transaction number so i get all of the records associated with those transactions but i've been having trouble doing that. Any suggestions or help would be really appreciated.

Thanks, below is what i have so far...

Public Sub TransactionInventory()

        SQLConnection.ConnectionString = connectionstring
            If SQLConnection.State = ConnectionState.Closed Then
            End If

            Dim Now As DateTime = DateTime.Now
            Dim sqlquery = "Select * from transaction_inventory where Transaction_Number = (Select Transaction_Number From transaction_inventory Where Bag_Skew = '" & TextBox1.Text & "')             
transaction_inventory.transaction_number = selected_transactions.transaction_number"
            Dim mycommand As New MySqlCommand()
            Dim myadapter As New MySqlDataAdapter()
            Dim table As New DataTable

            mycommand.Connection = SQLConnection
            mycommand.CommandText = sqlquery
            myadapter.SelectCommand = mycommand
            Dim mydata As MySqlDataReader
            DataGridViewAllTransactionInventory.DataSource = table
            mydata = mycommand.ExecuteReader()

            Catch ex As Exception
        End Try

Is This A Good Question/Topic? 0
  • +

Replies To: Help with sql query

#2 smohd  Icon User is offline

  • Critical Section
  • member icon

Reputation: 1822
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: Help with sql query

Posted 10 October 2011 - 02:02 PM

That is why we dont use = sign if we expect to get many results from sub query. Instead we use IN clause.
Although your query doesnt give sense to me, looking like redundant to me! because mostly all queries(sub & super) retrieve from the same table. It will give sense to have subquery if you query from two different tables
Was This Post Helpful? 0
  • +
  • -

#3 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 83
  • View blog
  • Posts: 364
  • Joined: 12-January 11

Re: Help with sql query

Posted 10 October 2011 - 02:10 PM

Select * 
from transaction_inventory 
where Transaction_Number = (Select Transaction_Number 
                            From transaction_inventory 
                            Where Bag_Skew = '" & TextBox1.Text & "'

This can't work if the nested query returns more than one result

try something like
Select Transaction_Number 
From transaction_inventory ti1 
Where Bag_Skew = '" & TextBox1.Text & "' Left Outer Join
      transaction_inventory ti2 on ti2.Transaction_Number = ti1.Transaction_Number

I don't know if I got the code quite right, but you get the point...a self join based on the input from the user.

EDIT: I also agree it looks redundant, but if you are really wanting to do this see my response. If I were you, I would consider just filtering based on the Bag_Skew and call it good, especially since that is what you are getting from the text box. If you were getting a transaction number, I would be more inclined to see why you want to do it like the way you are doing it.
Select *
From transaction_inventory
Where Bag_Skew=Textbox.Text

This post has been edited by Psyguy: 10 October 2011 - 02:16 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1