Help with SQL Query

[bemis82]

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
        Try
            SQLConnection.Close()
            If SQLConnection.State = ConnectionState.Closed Then
                SQLConnection.Open()
            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
            myadapter.Fill(table)
            DataGridViewAllTransactionInventory.DataSource = table
            mydata = mycommand.ExecuteReader()
            mydata.Close()
            SQLConnection.Close()

            Catch ex As Exception
            SQLConnection.Close()
            MsgBox(ex.ToString)
        End Try

 

[Menthos]

Without knowing the structure of the tables involved, it's a bit hard to help, however take a look at the keyword EXISTS, so your query would go something like:

SELECT [I]{fields}[/I] 
FROM transaction_inventory 
WHERE EXISTS (SELECT 1 FROM transaction_inventory WHERE Bag_Skew = '[I]{criteria}[/I]')

Obviously this may not be the approach, and I suspect that if we knew the table structures and possible data in the tables, it would turn out that you can achieve what you're after using a much more simple query.

Also take a look at one of this forum's contributors, jmcilhinney's, blog on parametizing queries - it's not good practice (and can lead to exploits) to dynamically add to a query with string concatenation.

 

[InertiaM]

Hope I have understood your problem correctly You werent a million miles away with your first attempt. However, points to note :-

1. Try and avoid using Subqueries. As far as I understand, subqueries will be run once for every record in your transaction_inventory table (using your query as an example). This is incredibly inefficient.

2. There arent that many occasions where you need to actually use a temporary table - you can do the same with an INNER JOIN.

3. DEFINITELY follow menthos' advice on parameterising queries - it will make your queries so much more readable in the long run.

Your point about using a temporary table is the nearest to my suggested solution. However, there is no need to actually create a temporary table - just do a version of a subquery.

SELECT
  transaction_inventory.Transaction_Number
FROM
  transaction_inventory
WHERE
  transaction_inventory.Bag_Skew = @BAG_SKEW_NUMBER
GROUP BY
  transaction_inventory.Transaction_Number

As a query on its own, this will produce all transactions related to the relevant Bag Skew Number. Now just expand it to give you what you want.

SELECT
  transaction_inventory.*
FROM
  transaction_inventory INNER JOIN
  (
    SELECT
      transaction_inventory.Transaction_Number
    FROM
      transaction_inventory
    WHERE
      transaction_inventory.Bag_Skew = @BAG_SKEW_NUMBER
    GROUP BY
      transaction_inventory.Transaction_Number
  ) Matched_Transactions ON transaction_inventory.Transaction_Number = Matched_Transactions.Transaction_Number

The big overhead difference is that the INNER JOIN query will be run once and once only, as opposed to your subquery that will be run once for every record in the transaction table. It then joins all transactions together based on the Transaction number.

 

[bemis82]

Ok great, it looks like it's working the way i wanted it to now. So thanks for the Help!