displaying dirrent fields in one field

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)

displaying dirrent fields in one field

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-03-10 : 07:12:42

hello,
I have 2 tables, customers and customers2.
there is a link in customers2 to customers.ID
If that link exists how can I display the info. in one field for either the customers.name and the customers2.name

eg,
customers table

ID Name
1 null
2 bob

customers2 table

ID Name CID
1 J1 1

I want to display the info like

name
J1
Bob

hope that makes sense,
thank for any help.
Jamie

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-10 : 07:18:06

SELECT a.Name
FROM CUSTOMERS a
WHERE EXISTS (SELECT b.* FROM CUSTOMERS2 b WHERE a.ID = b.ID)
UNION ALL
SELECT a.Name
FROM CUSTOMERS2 a
WHERE EXISTS (SELECT b.* FROM CUSTOMERS b WHERE a.ID = b.ID)

--**** Something like that

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-10 : 07:53:01

select ISNULL(a.name,b.name) as name
from customers a
left outer join customers2 b
on a.id = b.id

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-10 : 07:58:52

Nice!

I misunderstood the original question, please ignore my first post
Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-03-11 : 08:54:22

thanks guys for the response.
Jeff's post works correctly.

thank you.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-11 : 09:16:51

Depending on your SQL version and performance you can also use:

SELECT	COALESCE(a.name,b.name) as name
FROM	customers a LEFT OUTER JOIN customers2 b
	ON a.id = b.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources