If I have 2 tables (actually it would be the product of multiple joins) and
I am getting the same data out of both tables and unioning them, how would I
say if a person was in both tables I only wnat the data out of table x.

For example

Select Name,address,salary from candidate
union
Select Name,address,salary from Active

A person (Name) could be in both, but have different addresses or Salary.

I don't want to see both, but would like to say if he is in both only give
me the data from one table (say Candidate) and not the other.

Maybe a different way using case statement.

Thanks,

Tom

Here is one way:

SELECT Name, Address, Salary
FROM Candidate

UNION ALL

SELECT Name, Address, Salary
FROM Active
WHERE NOT EXISTS
(
    SELECT *
    FROM Candidate
    WHERE Active.Name = Candidate.Name
)


Show quoteHide quote

Show quote Hide quote This does what I want.

I had thought that I could do this with a Case statement, but this does the
job.

Thanks,

Tom
Show quoteHide quote

Show quote Hide quote Select
            Candidate.Name,
            Candidate.address,
            Candidate.salary
from
            Candidate
            inner join Active on
                (Active.Name = Candidate.Name)

Show quote Hide quote I tried that, but it only gives me the ones that match.

What I am looking for is all the records in both tables that match the
criteria (where clauses), but if there is a duplicate name, I want the
record from the Candidate file.

If I have the following:

drop table active
go

CREATE TABLE [dbo].[active] (
  [Name] [varchar] (50)  NULL ,
  [Address] [varchar] (50) NULL ,
  [Salary] [money] NULL
) ON [PRIMARY]
GO

drop table candidate
go

CREATE TABLE [dbo].[candidate] (
  [Name] [varchar] (50)  NULL ,
  [Address] [varchar] (50) NULL ,
  [Salary] [money] NULL
) ON [PRIMARY]
GO



insert candidate Values('tom','128 beach Ave',23.50)
insert candidate Values('Larry','950 Vermont',15.23)
insert candidate Values('Greg','15 mariott',120.50)
insert candidate Values('Frank','326 Warner',501.21)
insert active Values('John','226 Franklin',123.53)
insert active Values('Larry','332 Gregson',1.47)
insert active Values('Jason','991 Morris',111.33)
insert active Values('Ron','1 Wabash',29.50)

I want the results to be:

Name     Address         Salary
-------- --------------- ----------
Frank    326 Warner      501.21
Greg     15 mariott      120.50
Jason    991 Morris      111.33
John     226 Franklin    123.53
Larry    950 Vermont     15.23
Ron      1 Wabash        29.50
tom      128 beach Ave   23.50

(8 row(s) affected)

You'll notice that Larry from "332 Gregson" is not there as there is a
duplicate Larry on "950 Vermont" in the Candidate Table.

Thanks,

Tom
Show quoteHide quote

Storing Richtextbox data In Sql
Field value determines whether there is extra info that should be held about that record
sql statement formatting
Use of NULL and DEFAULT
Record locking within a stored procedure