UNION of 2 tables

  • CREATE TABLE Employees (

    Emp_ID CHAR(6) NOT NULL PRIMARY KEY,

    Last_name VARCHAR(20) NOT NULL,

    First_name VARCHAR(20) NULL,

    Address VARCHAR(40) NULL,

    City VARCHAR(32) NULL,

    State CHAR(2) NULL,

    Telephone_area_code VARCHAR(3) NULL,

    Telephone_number VARCHAR(16) NULL,

    EE01_Classification VARCHAR(60) NULL,

    Hire_date DATETIME NULL,

    Salary MONEY NULL,

    Gender NCHAR(1) NOT NULL,

    Age VARCHAR(3) NULL,

    Job_Title VARCHAR(60) NULL );

    CREATE TABLE Job (

    EEO_Classification VARCHAR(60) NULL,

    Job_title VARCHAR(50) NOT NULL PRIMARY KEY,

    Job_description VARCHAR(1000) NULL,

    Exemption_Status VARCHAR(20) NOT NULL );

    SELECT Last_name FROM employees

    UNION

    SELECT job_description FROM job;

    Any help is greatly appreciated!!

  • Is there a specific question? I suspect you're getting a truncation error, but it would be nice to know what the question actually is....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Any help for what? You did not ask any question or state what your problem is.

    What problem I see? First, "union" is used to return records from 2 or more tables. The columns returned from table1 must be of the same datatype and order as in table2.

    I don't think UNION is what you want. What you need is to JOIN the 2 tables. Go to books online and check it out.

    Which column(s) do you think you should join your tables on?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Sorry about that...it's not erroring out..it's only printing out the last_name and not giving me the job description from the job title. I suspect something to do with the way I have the primary keys set up but then again I'm not sure.

  • Then Alvin is on the right track - you're not looking for a UNION statement at all.

    A "UNION" essentially combines the rows from each query into a single query. So - you would find that your descriptions are listed in the LastName column "below" all of the names.

    Read up on JOIN instead - that's what you need.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I thought that too but I am taking a SQL Server class and the assignment specifically says to:

    Write a SQL query that uses UNION of the two tables to produce a third table.

    To me it seems like join is the way to do this but this is my first true taste of SQL and I admit I know just enough to be dangerous!!

  • munderhill73525 (3/17/2009)


    I thought that too but I am taking a SQL Server class and the assignment specifically says to:

    Write a SQL query that uses UNION of the two tables to produce a third table.

    To me it seems like join is the way to do this but this is my first true taste of SQL and I admit I know just enough to be dangerous!!

    Either a union or a join will give you a new table, but they do so in different ways. To perhaps oversimplify, a join will give you a new table wtih COLUMNS from each of the base tables. A union will give you a new table with ROWS from each of the base tables.

    In the example you provided it looks like what you are trying to do should be accomplished with a join, not a union.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • munderhill73525 (3/17/2009)


    I thought that too but I am taking a SQL Server class and the assignment specifically says to:

    Write a SQL query that uses UNION of the two tables to produce a third table.

    To me it seems like join is the way to do this but this is my first true taste of SQL and I admit I know just enough to be dangerous!!

    It does not make sense to me to ask beginners to: "Write a SQL query that uses UNION of the two tables to produce a third table."

    The resulting table would break the basic rules of database design.

    Am I missing something here?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Let me guess UoP (I used to teach the SQL Class)...

    You need to match the datatypes including sizes.

  • Alvin, I think it is a case of ensuring the students know the technique for doing it, even if it is one that they may not initially use as beginnings.

    Remember that while few beginners will run into them, there are plenty of valid reasons you would want to use union with good table design (creating partitioned views with large segregated data sets comes to mind right away as one example...)

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • timothyawiseman (3/19/2009)


    Alvin, I think it is a case of ensuring the students know the technique for doing it, even if it is one that they may not initially use as beginnings.

    Remember that while few beginners will run into them, there are plenty of valid reasons you would want to use union with good table design (creating partitioned views with large segregated data sets comes to mind right away as one example...)

    If you want students to learn how to use "UNION", especially to create a third table, then start with 2 tables that have more in common than the 2 listed in this post. These 2 tables only have 1 column in common. Combining these 2 into 1 third table using UNION makes absolutely no sense.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • If you want students to learn how to use "UNION", especially to create a third table, then start with 2 tables that have more in common than the 2 listed in this post. These 2 tables only have 1 column in common. Combining these 2 into 1 third table using UNION makes absolutely no sense.

    Totally agree, I used to tell my students that but you don't get much flexibility on what you can do in the assignments. FWTW I did point out to them how this question didn't make any sense, got nowhere with it.

  • Alvin Ramard (3/19/2009)


    timothyawiseman (3/19/2009)


    Alvin, I think it is a case of ensuring the students know the technique for doing it, even if it is one that they may not initially use as beginnings.

    Remember that while few beginners will run into them, there are plenty of valid reasons you would want to use union with good table design (creating partitioned views with large segregated data sets comes to mind right away as one example...)

    If you want students to learn how to use "UNION", especially to create a third table, then start with 2 tables that have more in common than the 2 listed in this post. These 2 tables only have 1 column in common. Combining these 2 into 1 third table using UNION makes absolutely no sense.

    I totally agree. Had I been writing an assignment like that I would have had very different tables to start with.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply