Select with a select
-
This is my multi word search SQL, but I also need to find the selling price of each item according to what market ID they have and then lookup their price for that market id . The first SELECT finds the Market ID which is then used by the 2nd SELECT to find the market_price. Can these 2 Select statements be placed within the large SQL in order to find the market price? If so, can someone tell me how to do it? strSQL = "Select Market_ID from CUSTOMER where ID = @CustID" strSQL = "Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID = @MarketID" strSQL = "SELECT DISTINCT dbo.PART.ID as ID, 'strSQL = "SELECT dbo.PART.ID as ID, strSQL = strSQL & "dbo.PART.DESCRIPTION, " strSQL = strSQL & "dbo.Part.PRODUCT_CODE, " strSQL = strSQL & "UNIT_PRICE AS Price, " strSQL = strSQL & "dbo.PART.COMMODITY_CODE, " ''strSQL = strSQL & "dbo.part_model.model_number, " strSQL = strSQL & "dbo.PART.STOCK_UM " ''strSQL = strSQL & "dbo.PART_CROSSREF.CrossrefID " strSQL = strSQL & "FROM dbo.PART " strSQL = strSQL & "INNER JOIN " strSQL = strSQL & "dbo.PART_CROSSREF ON dbo.PART.ID = dbo.PART_CROSSREF.ID " strSQL = strSQL & "INNER Join " strSQL = strSQL & "dbo.PART_MODEL ON dbo.PART.PRODUCT_CODE = dbo.PART_MODEL.PRODUCT_CODE " strSQL = strSQL & " WHERE ((PART.ID like '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" For CountLoopsForSQL = 0 To UBound(KeywordsForSearch) strKeyWord = KeywordsForSearch(CountLoopsForSQL) If strKeyWord <> "" Then ' Search for the words in the "ID" field strSQL = strSQL & " OR (dbo.PART_CROSSREF.crossrefID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" strSQL = strSQL & " OR (dbo.PART_CROSSREF.ID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Product_code" field strSQL = strSQL & " OR (dbo.Part.Product_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Product_code" field strSQL = strSQL & " OR (dbo.Part.description LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Commodity_code" field strSQL = strSQL & " OR (dbo.Part.Commodity_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" End If Next
-
This is my multi word search SQL, but I also need to find the selling price of each item according to what market ID they have and then lookup their price for that market id . The first SELECT finds the Market ID which is then used by the 2nd SELECT to find the market_price. Can these 2 Select statements be placed within the large SQL in order to find the market price? If so, can someone tell me how to do it? strSQL = "Select Market_ID from CUSTOMER where ID = @CustID" strSQL = "Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID = @MarketID" strSQL = "SELECT DISTINCT dbo.PART.ID as ID, 'strSQL = "SELECT dbo.PART.ID as ID, strSQL = strSQL & "dbo.PART.DESCRIPTION, " strSQL = strSQL & "dbo.Part.PRODUCT_CODE, " strSQL = strSQL & "UNIT_PRICE AS Price, " strSQL = strSQL & "dbo.PART.COMMODITY_CODE, " ''strSQL = strSQL & "dbo.part_model.model_number, " strSQL = strSQL & "dbo.PART.STOCK_UM " ''strSQL = strSQL & "dbo.PART_CROSSREF.CrossrefID " strSQL = strSQL & "FROM dbo.PART " strSQL = strSQL & "INNER JOIN " strSQL = strSQL & "dbo.PART_CROSSREF ON dbo.PART.ID = dbo.PART_CROSSREF.ID " strSQL = strSQL & "INNER Join " strSQL = strSQL & "dbo.PART_MODEL ON dbo.PART.PRODUCT_CODE = dbo.PART_MODEL.PRODUCT_CODE " strSQL = strSQL & " WHERE ((PART.ID like '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" For CountLoopsForSQL = 0 To UBound(KeywordsForSearch) strKeyWord = KeywordsForSearch(CountLoopsForSQL) If strKeyWord <> "" Then ' Search for the words in the "ID" field strSQL = strSQL & " OR (dbo.PART_CROSSREF.crossrefID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" strSQL = strSQL & " OR (dbo.PART_CROSSREF.ID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Product_code" field strSQL = strSQL & " OR (dbo.Part.Product_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Product_code" field strSQL = strSQL & " OR (dbo.Part.description LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" ' Search for the words in the "Commodity_code" field strSQL = strSQL & " OR (dbo.Part.Commodity_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')" End If Next
-
Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID =(Select Market_ID from CUSTOMER where ID = @CustID) ????
I appreciate the feedback but I had never used a Select within a Select and so not only did I not know what syntax was acceptable, I also didn't know where I could put the Selects. I have since found that I can place them almost anywhere I want. However, your example did show me a way to use a Select in a way I didn't know it could be done. That will be useful info for the future.