Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: merge result of two tables

    Hey all
    Suppose we have two tables with these structure

    tblGallery

    picid - int
    picture - varchar
    adddate - date time



    tblNews

    nid - int
    subject - varchar
    body - text
    adddate - date time


    I want merge result of these tables and show them together in my application , in order by date which the record has been added to the talbe

    for example

    1. A picture from tblGallery
    2. News From tblnews
    3. News From tblnews
    4. A picture from tblGallery
    5. A picture from tblGallery
    6. A picture from tblGallery
    7. News From tblnews
    8. A picture from tblGallery
    9. News From tblnews

    I can not use UNION or any kind of that , BECAUSE i want to know which result is from which table, coz I want to create proper HTML codes for each record which I get from database , so I mean I have to know if the result is from tblGallery or tblNews

    is there any way to do this in sql or does anyone have any idea to make it to work

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by 4tech
    I can not use UNION ...
    yes you can

    Code:
    SELECT 'gallery' AS row_type
         , picid     AS id
         , picture   AS entity
         , adddate 
         , NULL      AS extra
      FROM tblGallery_tbl_table
    UNION ALL
    SELECT 'news' 
         , nid
         , subject
         , adddate 
         , body
      FROM tblNews_tbl_table
    ORDER
        BY adddate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    13
    Thank you so much I think it works but what if I want to merge more than two tables with this method ?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Just continue using UNION ALL ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jan 2009
    Posts
    13
    Thank you all
    If i had any other question post it here

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    another question
    what if I wanted to get only top 10 records of second table
    this works fine

    Code:
    Select top 60 * FROM (
    SELECT 'gallery' AS row_type
         , picid     AS id
         , picture   AS entity
         , adddate 
         , NULL      AS extra
      FROM tblGallery_tbl_table
    UNION ALL
    SELECT 'news' 
         , nid
         , subject
         , adddate 
         , body
      FROM tblNews_tbl_table
    ) as myquery
    ORDER
        BY adddate
    but I want to get top 10 of the second table and get the rest form the other table

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    4tech, try this and see what happens:
    Code:
    Select * 
    FROM (
          SELECT top 60 
                'gallery' AS row_type
               , picid     AS id
               , picture   AS entity
               , adddate 
               , NULL      AS extra
          FROM tblGallery_tbl_table
          UNION ALL
          SELECT TOP 10 
                'news' 
               , nid
               , subject
               , adddate 
               , body
          FROM tblNews_tbl_table
         ) as myquery
    ORDER BY adddate
    If you want 60 total rows, change the first queries top to 50.

  8. #8
    Join Date
    Jan 2009
    Posts
    13
    doesn't work
    it gives me some random records that is not sorted properly, this is why I used select top in outter select to fix this , now I want get only 10 from second table and the rest from other table
    Last edited by 4tech; 06-07-09 at 19:20.

  9. #9
    Join Date
    Jan 2009
    Posts
    13
    someone help me plz

  10. #10
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Code:
    Select * 
    FROM (
          SELECT
                'gallery' AS row_type
               , picid     AS id
               , picture   AS entity
               , adddate 
               , NULL      AS extra
          FROM (SELECT TOP 60 * FROM tblGallery_tbl_table ORDER BY adddate) sub1
          UNION ALL
          SELECT 
                'news' 
               , nid
               , subject
               , adddate 
               , body
          FROM (SELECT TOP 10 * FROM tblNews_tbl_table ORDER BY adddate) sub2
         ) as myquery
    ORDER BY adddate
    Maybe...

  11. #11
    Join Date
    Jan 2009
    Posts
    13
    doesn't work

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Windowed functions
    Code:
    SELECT 'gallery' As row_type
         , picid As id
         , picture As entity
         , adddate
         , NULL As extra
    FROM   (
            SELECT picid As id
                 , picture
                 , adddate
                 , Row_Number() OVER (ORDER BY adddate DESC) As row_num
            FROM   tblGallery_tbl_table
           ) As a_subquery
    WHERE  row_num <= 60
    
    UNION ALL
    
    SELECT 'news'
         , nid
         , subject
         , adddate
         , body
    FROM   (
            SELECT nid
                 , subject
                 , adddate 
                 , body
                 , Row_Number() OVER (ORDER BY adddate DESC) As row_num
            FROM   tblNews_tbl_table
           ) As another_subquery
    WHERE  row_num <= 10
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2009
    Posts
    13
    oh yeah
    that works good,
    Thanks a lot buddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •