none
how to merge two queries' results?

    Question

  •  

    hi,

    my first query is:

    "SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _

     TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _

     where TBL_BARKOD.barkod=@barkod"

     

     

    second query :

     

    "SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _

     where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "

     

    i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1)

    second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that  returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)

    Monday, November 12, 2007 12:56 PM

All replies

  • Hi Sevilaycelik

     

    Thanks for using our product!

    You might want to check out the new QP enhancements that have been made in Version 3.5 of our product.

    Core Database Functionality

    1. Implements the timestamp (rowversion) data type.
    2. Support for Transact-SQL statements has been extended as follows:
      • Nested query in SELECT FROM clause
      • CROSS APPLY and OUTER APPLY
      • CAST and DECIMAL
      • TOP
      • SET IDENTITY INSERT

    As nested query has been supported inside the from clause, you can make your both the queries as two table sources then merge with whatever operator or join you want.

    Please install the books online with the product to get latest information on their usage.

     

    I am putting up a small example for you:

    select * from ((select [customer id], [company name] from customers where [customer id] = 'ALFKI') Tab1 cross apply (select [product id] from products where [product id] = 1) Tab2)

     

    Here the your first query is Tab1 and Second query will be Tab2. You did not mention how you want to merge them. I have used simple cross apply here. After the whole result is generated you can use more filtering options using 'where clause' to get desired result set.

     

    Hope this helps.

     

    Ravi

    Wednesday, February 06, 2008 9:37 AM
  • If both queries are returning only one record each, the following will work. If the queries returns more than one record, you will get M * N records (where M and N are the recordcount of each query)

     

    Code Snippet

    SELECT * FROM (

    SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1

    FROM TBL_STOK INNER JOIN TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id

    where TBL_BARKOD.barkod=@barkod

    ) A CROSS JOIN (

    SELECT TBL_STOKDEPO.fiyat1

    FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id

    where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod

    ) B

     

     

    Wednesday, February 06, 2008 11:46 AM
  • hi jacob,

    u're correct about the M*N results. why is that happened? i'm trying to merge a few queries (user defined functions) into one resultset but cannot get the result I desired. In my case, not all results from these functions return result. Thus, if I have 4 functions, the resultset would be A * B * C * D, which is not the desired result. like I said, A or B or C or D could return no row, then the resultset would be 0 as well. How can I overcome this? Thanks in advance.

    Regards,
    Shah
    Tuesday, April 29, 2008 2:18 AM
  • Shah,

    I am not sure if I have clearly understood what you are looking for. Could you post some samle script and data and explain the problem?

     

    regards

    Jacob

    Tuesday, April 29, 2008 6:08 AM
  • Shah,

     

    from what you have tried to explain here, I feel there is an issue with the join conditions you are using between your different functions.

     

    Please try to run all your function A,B,C,D individually and check if you get any results.

     

    Later on try to use operators and join which don't effect your final result set even if any of the above is null and does not return any row.

    A cross join or an "OR" operator might be of help. If you try to use inner join between them and there are no rows, the result set is going to be empty.

     

    Hope this helps a bit. Else please give us some sample as Jacob asked.

    Tuesday, April 29, 2008 7:32 AM
  •  Ravi Tandon MSFT wrote:

    Hi Sevilaycelik

     

    You might want to check out the new QP enhancements that have been made in Version 3.5 of our product.

     

     

    Hi Ravi. Until you guys include 3.5 in a platform builder update, it doesn't exist. Any news on when we can expect that to happen?

    Wednesday, April 30, 2008 12:37 AM
  • I can't really comment on the Platform Builder Update, it should be coming soon.

     

    The download of setup is available on microsoft website:

    http://www.microsoft.com/sql/editions/compact/default.mspx

     

    You can get 3.5 from there and deploy seperately for time being.

    Friday, May 02, 2008 10:11 AM
  • I think I read on someone's blog that they were skipping the update for April (which looks like the case.) Can you get some word on when it is going to be available? Because really, SQL CE 3.5 doesn't exist yet for CE 5 or CE 6, its primary target platform. Might as well not even shipped it. You guys need to get together with the Platform Builder group and start slapping some heads, because this is really absurd. Think of it this way, imagine people were still waiting to get their hands on VS 2008 for some completely unknown reason, even though it was RTM 6 months ago. I suspect that the VS program manager would be fairly irate.

    Friday, May 02, 2008 5:14 PM