Resolved: Dataset with JOIN Sql Query.... how?

[Redth]

Hello,

I've been learning to work with DataSets and DataTables a bit in the past day or 2.. i despised the thought of moving from my Recordsets from ADO to these crazy datasets and tables... but upon researching a bit, they're quite nice actually...

anyways, i can query a table with a SqlDataAdapter, then Fill a Dataset with it... like such:

Public Shared Function GetProducts(ByVal intUserID As Integer) As DataTable
    Dim objConn As SqlConnection
    Dim dadProducts As SqlDataAdapter
    Dim dstProducts As DataSet
    Dim dtblProducts As DataTable
 
    Dim strSQL As String
 
    'Set SQL Query
    strSQL = "SELECT ID, Product_Name, Product_DescText, Expire_Date FROM Products WHERE CustomerID = " & intUserID
 
    'Set Connection
    objConn = New SqlConnection(Database.ConnectionString)
 
    'Create DataAdapter with SQL Query
    dadProducts = New SqlDataAdapter(strSQL, objConn)
 
    'Create Dataset and fill it with results
    dstProducts = New DataSet()
    dadProducts.Fill(dstProducts, "Products")
 
    'Return Datatable
    GetProducts = New DataTable()
    GetProducts = dstProducts.Tables("Products")
End Function

that works all fine because i'm selecting from only one table...

but what happens if i have a sql query with say a couple inner joins in it?

actually i have a very complex query with 2 inner joins in it, and another select statement inside an IN() clause... so, it's quite involved...

how do i return something like this into a dataset?

here's my query:



SELECT Products.ID As Prod_ID, Products.Product_Name As Prod_Name, Products.Product_DescText As Prod_Desc, Category.ID As Cat_ID, Category.Category As Cat_Cat, SubCategory.ID As SubCat_ID, SubCategory.SubCategory As SubCat_SubCat FROM Products INNER JOIN Category ON Category.ID = Products.CategoryID INNER JOIN SubCategory ON SubCategory.ID = Products.SubCategoryID WHERE DATEDIFF(dd, GETDATE(), Products.Expire_Date) > 0 AND Products.IsVisible <> 0 AND Products.CustomerID IN (SELECT ZipCodes.UserID FROM ZipCodes WHERE DATEDIFF(dd, GETDATE(), ZipCodes.ExpireDate) > 0 AND ZipCodes.Visible <> 0 AND ZipCodes.ZipCode = '11111') AND Products.Product_Keywords LIKE '%Keywords%' ORDER BY Category.Category, SubCategory.SubCategory, Products.Product_Name



Mostly what i don't understand is what tablename to put in the dadProducts.Fill(dstProducts, "Products") line... in the above example, it's Products, the table i'm selecting from.. but what would it be in that query with JOINS ?

Same goes for populating the DataTable with the Dataset.Tables("TableName")...

now, i've had enough difficulty figuring this query out to work how it is... i'm afraid i might have to do something with temp tables, or something like that, which i know nothing about

please help!