none
Merge Data from different tables?

    Question

  • Hi,

    i have a question on merging data between tables. For example

    1. table -> goods

    2. table -> cars

    I would like to merge these two different tables in one view or anything else.

    table goods structure

    ID     TITLE     FAMILY     AREA

    table cars

    ID     TITLE     CLASS

    I would like to merge these two tables to a 3rd Table/View etc.  with this structure

    ID     TITLE     TYPE         FAMILY     CLASS

    1      APPLE     GOODS    APPLES     -

    2      BENTLEY CARS        -               SEDAN

    Is this possible?


    Best Regards
    Bog

    Developers Field Notes | www.bog1.de

    Saturday, July 26, 2014 4:40 PM

Answers

  • SELECT COALESCE(t1.ID,t2.ID) AS ID,
    COALESCE(t1.TITLE,t2.TITLE) AS TITLE,
    COALESCE(t1.TYPE,t2.TYPE) AS TYPE,
    COALESCE(t1.FAMILY,t2.FAMILY) AS FAMILY,
    COALESCE(t1.CLASS,t2.CLASS) AS CLASS

    FROM ( SELECT ID, TITLE, 'GOODS' AS TYPE, FAMILY, '' AS CLASS FROM goods )t1 FULL OUTER JOIN (
    SELECT ID,   TITLE, 'CARS' AS TYPE,  '' AS FAMILY, CLASS
    FROM cars
    )t2
    ON 1=2



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 26, 2014 6:54 PM

All replies

  • SELECT ID,   TITLE, 'GOODS' AS TYPE,  FAMILY, '' AS CLASS
    FROM goods
    
    UNION ALL
    
    SELECT ID,   TITLE, 'CARS' AS TYPE,  '' AS FAMILY, CLASS
    FROM cars
    Something like this?

    Saturday, July 26, 2014 5:49 PM
  • SELECT COALESCE(t1.ID,t2.ID) AS ID,
    COALESCE(t1.TITLE,t2.TITLE) AS TITLE,
    COALESCE(t1.TYPE,t2.TYPE) AS TYPE,
    COALESCE(t1.FAMILY,t2.FAMILY) AS FAMILY,
    COALESCE(t1.CLASS,t2.CLASS) AS CLASS

    FROM ( SELECT ID, TITLE, 'GOODS' AS TYPE, FAMILY, '' AS CLASS FROM goods )t1 FULL OUTER JOIN (
    SELECT ID,   TITLE, 'CARS' AS TYPE,  '' AS FAMILY, CLASS
    FROM cars
    )t2
    ON 1=2



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 26, 2014 6:54 PM