combine 3 sql statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

combine 3 sql statements

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-06-09 : 13:50:06

I want to combine 3 sql statements and order by one field.

For example to combine the follwowing:

select lname,fname from clients

select lname,fname from employees

select lname,fname from contacts

and have them returned in one recordset ordered by lname.

Is this possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-09 : 13:51:10

select lname,fname from clients
union all
select lname,fname from employees
union all
select lname,fname from contacts
order by lname

Tara

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-06-10 : 02:35:32

Thanks. Easier then I thought. So I can make 3 views (so they all have the same field names) and then just join them all?

quote:
Originally posted by tduggan

select lname,fname from clients
union all
select lname,fname from employees
union all
select lname,fname from contacts
order by lname

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-10 : 12:30:53

Sure, but show us what your schema looks like now and we'll be able to help you better.

Tara

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-06-11 : 03:49:49

I have two views I want to join together. They have almost the same fields except maybe one or two extra in each. In the query I would like the extra fields to be blank when there is no field from that query. I also want a field called cat and if it is from the vwEmployee then cat will=employee and if from vwClients cat=clients.

can someone help me with the code?

Here's the code from both views:

SELECT ClientContactInfo.lname, ClientContactInfo.fname, Clients.address, Clients.city,
Clients.state, Clients.zip, Clients.country, Clients.organization,
ClientContactInfo.homephone, ClientContactInfo.workphone, ClientContactInfo.cellphone, ClientContactInfo.email FROM Clients RIGHT OUTER JOIN
ClientContactInfo ON Clients.pcontactinfoid = ClientContactInfo.cinfoID AND Clients.scontactinfoid = ClientContactInfo.cinfoID

SELECT lname, fname, street, city, state, zip, country, homephone, workphone, cellphone, email
FROM Employees

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources