none
請教UNION ALL加搜尋條件問題 RRS feed

  • 問題

  • 請教各位先進

     

    下面SQL1的語法是可以正確讀出資料的
      而我想再對Tprodspec做關鍵字搜尋

    Tprodspec是另外從Products資料表來的
      卻無法讀出符合條件的資料(SQL2)
      請問我的語法是否有誤
      謝謝

     

    Code Snippet

    --SQL1

    SELECT StocksList.StocksID AS Tsid, StocksList.ProductsSn AS Tpsn, StocksList.DepotID AS Tdid, StocksList.Prodcounts AS Tps, StocksList.TbType AS Ttype, (SELECT StocksDate FROM Stocks WHERE StocksID = StocksList.StocksID) AS Tdate, (SELECT ProdSpec FROM Products WHERE Sn = StocksList.ProductsSn) AS Tprodspec FROM StocksList

    WHERE (StocksList.TbType <>'')

    UNION ALL

    SELECT SellsList.SellsID, SellsList.ProductsSn, SellsList.DepotID, SellsList.Prodcounts, SellsList.TbType AS Ttype, (SELECT SellsDate FROM Sells WHERE SellsID = SellsList.SellsID), (SELECT ProdSpec FROM Products WHERE Sn = SellsList.ProductsSn)  FROM SellsList

    WHERE (SellsList.TbType <>'')

    UNION ALL

    SELECT AdjustStocks.AdjID, AdjustStocks.ProductsSn, AdjustStocks.DepotID, AdjustStocks.Prodcounts, AdjustStocks.TbType AS Ttype, AdjustStocks.AdjDate, (SELECT ProdSpec FROM Products WHERE Sn = AdjustStocks.ProductsSn) FROM AdjustStocks

    WHERE (AdjustStocks.TbType <>'')

    ORDER BY  Tdate DESC

     

    --SQL2

    SELECT StocksList.StocksID AS Tsid, StocksList.ProductsSn AS Tpsn, StocksList.DepotID AS Tdid, StocksList.Prodcounts AS Tps, StocksList.TbType AS Ttype, (SELECT StocksDate FROM Stocks WHERE StocksID = StocksList.StocksID) AS Tdate, (SELECT ProdSpec FROM Products WHERE Sn = StocksList.ProductsSn) AS Tprodspec FROM StocksList

    WHERE (StocksList.TbType <>'') AND (StocksList.Tprodspec LIKE '%6201ZZ%')

    UNION ALL

    SELECT SellsList.SellsID, SellsList.ProductsSn, SellsList.DepotID, SellsList.Prodcounts, SellsList.TbType AS Ttype, (SELECT SellsDate FROM Sells WHERE SellsID = SellsList.SellsID), (SELECT ProdSpec FROM Products WHERE Sn = SellsList.ProductsSn) AS Tprodspec  FROM SellsList

    WHERE (SellsList.TbType <>'') AND (SellsList.Tprodspec LIKE '%6201ZZ%')

    UNION ALL

    SELECT AdjustStocks.AdjID, AdjustStocks.ProductsSn, AdjustStocks.DepotID, AdjustStocks.Prodcounts, AdjustStocks.TbType AS Ttype, AdjustStocks.AdjDate, (SELECT ProdSpec FROM Products WHERE Sn = AdjustStocks.ProductsSn) AS Tprodspec  FROM AdjustStocks

    WHERE (AdjustStocks.TbType <>'') AND (AdjustStocks.Tprodspec LIKE '%6201ZZ%')

    ORDER BY  Tdate DESC

     

    2007年5月23日 上午 02:13

解答

  • 要否用inner join試看看.

     

    SELECT a.StocksID AS Tsid, a.ProductsSn AS Tpsn, a.DepotID AS Tdid, a.Prodcounts AS Tps, a.TbType AS Ttype, (SELECT StocksDate FROM Stocks WHERE StocksID = a.StocksID) AS Tdate, b.ProdSpec AS Tprodspec

    FROM StocksList a

    inner join Products b on b.Sn =a.ProductsSn

    WHERE (a.TbType <>'') AND (b.Tprodspec LIKE '%6201ZZ%')

    2007年5月25日 上午 03:19

所有回覆

  • 請教各位先進

     

    在做union all時去select另一個table並指定為Tprodspec

    而要針對Tprodspec做search時

    要如何下正確語法

    謝謝

     

    2007年5月23日 上午 08:20
  • 要否用inner join試看看.

     

    SELECT a.StocksID AS Tsid, a.ProductsSn AS Tpsn, a.DepotID AS Tdid, a.Prodcounts AS Tps, a.TbType AS Ttype, (SELECT StocksDate FROM Stocks WHERE StocksID = a.StocksID) AS Tdate, b.ProdSpec AS Tprodspec

    FROM StocksList a

    inner join Products b on b.Sn =a.ProductsSn

    WHERE (a.TbType <>'') AND (b.Tprodspec LIKE '%6201ZZ%')

    2007年5月25日 上午 03:19
  • 謝謝Jeff大的回覆與指導

    謝謝您

     

    2007年5月28日 上午 02:12