|
I'm new to SQL and trying to understand what is most likely a simple question.
I'm trying to use UNION to pull together a result set of Name and Employee Number. Unfortunately there are duplicates in the two tables. The UNION syntax is supposed to weed out duplicates, but I have this situation.
First Name Last Name ID
TOM JONES 1111
TOM L JONES 1111
The code below shows what I have. The UNION evidentally looks at all the fields you bring back with the select. I need it to UNION only on the EmpID, but also have the name in the result list. I don't care which table it pulls the name, because either way, the name should be basically alike.
SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
ORDER BY LastName ASC,FirstName ASC, EmpID ASC
Any suggestions how to UNION the information while eliminating duplicates based on EmpID only? Any help is appreciated!! 
Lost in the vast sea of .NET
|
|
|
|
|
SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL and
EmpID not in (select empID from table2)
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
where EmpID not in (select empID from table1 where EmpTermDate IS NULL)
ORDER BY LastName ASC,FirstName ASC, EmpID ASC
"People who never make mistakes, never do anything."
|
|
|
|
|
UNION is used to combine results from two or more selects. You cannot use it to remove duplicates (although if you specify SELECT DISTINCT it will remove duplicate records).
From your example it seems that you want to show all employees from both tables, but only one distinct employee name for each EmpID.
You may want to use one table as the primary table (to get only one name) and get the rest of the employees from the secondary table.
Here's how you can return all employees from both tables:
SELECT LastName, FirstName, EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
<code>AND SocSecNo NOT IN (SELECT EmpNo FROM table1)</code>
ORDER BY LastName ASC, FirstName ASC, EmpID ASC
If both tables contain the same number of employee id's (i.e. they all match), then you can use INNER JOIN and get the name from one table only.
SELECT table1.LastName, table1.FirstName, EmpNo
FROM table1
INNER JOIN table2 ON table1.EmpNo = table2.SocSecNo
WHERE table1.EmpTermDate IS NULL
ORDER BY LastName ASC, FirstName ASC, EmpID ASC
There are other ways to return just one name from both tables, but this one works and it returns all employees from both tables.
Hope it helps 
|
|
|
|
|
Well, I’ve posted my question on two message boards and received several suggestions. First of all Thanks for all your feedback and help!!
Below are the two approaches that I’ve received and I’ve tweaked them to get the result set I was looking for. Table one includes everyone on our payroll including terminated employees. Table two is our health insurance table which includes retirees. The emp number will match especially with the retirees. The problem also comes into play when you have retirees come back to work on a contractual basis. Somedays I wish they would just go golfing. 
Hopefully my explanation above helps in understanding the data I’m trying to select. Both of the selects below work where we include all the active folks on our payroll (table1), plus all the active health insurance participants (table2). Both code snipplets also weed out the duplicate Retirees and only list them once.
I now have the question; Is one section of code using a stored procedure is more efficient than the other? I’ve tested both selects in my VB.NET project and the second code snipplet is about 50 milliseconds faster. My record count is 1578. Even if the second code snipplet is faster, is it more efficient to use the join? Nobody will really notice 50 milliseconds, so I want to go with the code that will take the least amount of resources from SQL. I used Query analyzer to perform an Estimated Execution Plan and didn’t notice any real differences. Of course I’m new to this, and not sure what I’m looking at.
Any suggestions or feedback is appreciated!! Again, thanks for the great feedback you’ve already given!!
SELECT [table1].LastName, [table1].FirstName, [table1].EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT [table2].LastName, [table2].FirstName, [table2].SocSecNo AS EmpID
FROM table2
LEFT JOIN [table1] ON [table2].SocSecNo = [table1].EmpNo
WHERE [table1].EmpNo IS NULL
OR
([table1].EmpNo IS NOT NULL
AND
[table1].EmpTermDate IS NOT NULL)
ORDER BY LastName ASC, FirstName ASC, EmpID ASC
SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
WHERE SocSecNo NOT IN (SELECT EmpNo FROM table1 WHERE EmpTermDate IS NULL)
ORDER BY LastName ASC,FirstName ASC, EmpID ASC
Lost in the vast sea of .NETNET
|
|
|
|
|
The second statement is definitely faster as you have a shorter WHERE condition compared to the first one.
You should first choose whichever is logically correct, and if both are correct, then choose the simplest one.
BTW, from what I see your first statement can be replaced with the second, as it does the same thing anyway.
First statement: select table1 excluding retiree UNION select table2 where records not in table1 or records are retiree.
Second statement: select table1 excluding retiree UNION select table2 where records not in table1 (which includes retiree).
Hope that helps,
Edbert
|
|
|
|
|
I'm going to go with the second statement. It is logically correct and I believe more readable and it is faster.
I can't take out the first part of the select. Table 1 is our payroll table which has 4158 records with around 1400 active employees. Table 2 contains our retirees, COBRA participants and 2 agencies who have health insurance, but are not on our payroll. Table 2 has 606 records. 9 of the records on table 2 are retirees who have come back as active contractual employees. If I just use the second portion of the select statment, the result list is 597 which contains all the health insurance records with the exception of the 9 duplicates. The first portion of the select pulls back all the active employees including the 9 retirees who should be golfing, but their working contractually.
With everyone's feedback, I believe I have a good piece of code for this lookup. I took a SQL course about a year and a half ago, and now I'm finally getting into my first ADO project. Thanks for the feedback! I really appreciate everyone's help. 
Lost in the vast sea of .NET
<a href="http://www.komputing.com/Pricelist.html">Visit my website at www.komputing.com</a>
|
|
|
|
|
Hi All
I have already checked the answers in discussions to find a solution for my problem but I could not..
I am using Trancation Replication for my customer. There is one server (Publish) in a city and 5 more Subscription in different citys. We are only replicating one Table, that is called "Adresse"
So far I got 2 kind of problems.
1.One of them subscriptions being "inactive".
2.the other one is that some of the subscriptions have connection problem with Publisher DB.
For the Problem 1, if there is no transaction occurs within "14 days(default value)" between publishing server and subscription servers, becoming Subscriptions inactive. I changed this value as "never expired" but I am not sure it is a good idea. As I read, Distribution DB is growing. But only one Table is it a problem?
The 2. Problem that I yesterday had is connection Problem. 3 of subscriptions was working fine but 2 of them were not available.
The error messages are at replication monitor like this for these 2 server.
"The agent is suspect.No Response within last 10 minutes"
I know that is only a information messages.
But in subcription side, the error was;
"The Process could not connect to Distributor 'Server1' "
The customers Network is really bad and they are living disconnections very often.
Then, in replication monitor screen, I made right click to subcriptions which are not running and clicked "Start synchronizing".
After that, the transactions are made and the situations of these 2 subscriptions became normal and active.
Regarding this problem, if there is a network problem in Subcriptions, how can replication (synchronizing) start automatically, after the network is up again.
Because, its not good, everytime connecting to Customer server and start synchronizing. Could it be made with and stored procedures or with a Job, script??
Thanks alot in advance...
Tolga
Tolga YARAMIS
|
|
|
|
|
I'm updating a MSAcess app to run on SQL with a .NET frontend. Part of the app needs to create a temporary table and export its contents to DBF file. I want the same functionality in my new app, without the user having to go anywhere near Enterprise Manager.
In Access I used a pre made header file (blank dbf file with column names and no data basically) which I linked to a temp table and populated that with an insert query - this filled the dbf file for me. All I needed to do was delete the temp table and hand the DBF file to the end user.
Any pointers where to start please ....
Tks
Dave
Tks
Dave
|
|
|
|
|
To insert two table with the same fields into one table using stored procedure.
table A with field E and R
table B also have the same fields with table A
result:
table C with the records from table A and B. Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Never mind I figured it out already by using Insert into, But now I'm having String or binary data would be truncated. error .
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
string or binary data would be truncated means that the length of the string you are trying to insert is > then the length of the column.
For example,
Maybe the column length is 10 and you are trying to insert a 12 char string.
"People who never make mistakes, never do anything."
|
|
|
|
|
|
create or replace view YOUR_VIEW as
select SOME_COLUMN_LIST
from SOME_TABLE_LIST
where CONDITIONS
;
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
create viewname (Syntex of the SQL)
eg create viewname (Select * from emp)
|
|
|
|
|
how to save pic in your database
|
|
|
|
|
Here ya go. FYI, it's more efficient to save a pic in a file directory rather then a database.
Dim bufferLen As Integer
Dim fs As System.IO.FileStream
Dim br As System.IO.BinaryReader
Dim buffer() As Byte
bufferLen = 10000000
fs = New System.IO.FileStream(fileName, FileMode.Open, FileAccess.Read)
br = New System.IO.BinaryReader(fs)
buffer = br.ReadBytes(bufferLen)
br.Close()
fs.Close()
Dim sConn As New String(strConnection)
Dim Con As New SqlConnection(sConn)
Dim sqlcmd As SqlCommand
sqlcmd = New SqlCommand("INSERT INTO Picture (Picture) VALUES (@Picture)", Con)
Dim dataParm1 As SqlParameter
dataParm1 = sqlcmd.Parameters.Add("@Picture", System.Data.SqlDbType.Image, buffer.Length)
dataParm1.Value = buffer
Con.Open
sqlcmd.ExecuteNonQuery()
Con.Close()
"People who never make mistakes, never do anything."
|
|
|
|
|
Hi,
I am binding the database column with dropdownlist box.It is working fine.
But i have to set particular value as my first value in the dropdownlist box.
for example, the coulmn values are
Windows 2003
Unix
Solaris
Linux
Windows 2000
Windows XP.
I want to show Linux as my first value in the dropdownlist box, then other values.
Linuxt may be 5 th record or 24 th record or anywhere in the table.
I have used simple query now. "SELECT CategoryName From Category".
Can any one help me to write a query.
Thanks & Appriciate You.
|
|
|
|
|
Since you require a sort order and that sort order is not the lexical order of the text, you can add a column to the table called, .. Sort_Order. Load that column with unique numeric values and then your query becomes "Select CategoryName from Category order by Sort_Order".
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
Thanks for your reply,
My table has only two column one is automatic(identity) column and other is CategoryName.
i can't add any more column this is the problem, so how can i do this.
Thanks
|
|
|
|
|
Perhaps another way would be to not bind a datatable to the control, but to loop manually around the datatable adding the data items to the control. Put an IF clause in to look out for the item that you want and insert this into the first position of the control.
Shawn
|
|
|
|
|
I have a recordset and want to push this one into a database. Is there any way to do it without a recordset loop ?
|
|
|
|
|
You can look into the BCP command in SQL Server. If you are using VB I think you can do a rs.Save to save it to a text file. It has been a while since I used VB so can't remember if you can set the delimited for the file or not. If you are using .Net, not sure. I do know you can save it to an xml file using the dataset object. I haven't notice the capability to save to disc in any other format though, but it might be there.
Also, triggers don't fire when you use BCP.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Why should i use databinding in my forms while coding with business objects is easier for me?
|
|
|
|
|
|
Please tell me!
How can i get first 5 records in SELECT query? (I'm using SQL server 2000)
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
