March 17, 2009 at 11:21 am
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!!
March 17, 2009 at 11:36 am
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?
March 17, 2009 at 11:37 am
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?
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]
March 17, 2009 at 11:39 am
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.
March 17, 2009 at 11:43 am
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?
March 17, 2009 at 11:46 am
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!!
March 17, 2009 at 11:56 am
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/
March 17, 2009 at 2:41 pm
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?
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]
March 18, 2009 at 3:45 pm
Let me guess UoP (I used to teach the SQL Class)...
You need to match the datatypes including sizes.
March 19, 2009 at 6:38 am
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/
March 19, 2009 at 7:05 am
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.
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]
March 19, 2009 at 7:22 am
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.
March 20, 2009 at 6:05 am
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