Home » SQL & PL/SQL » SQL & PL/SQL » How to get data from another table (if its there)
How to get data from another table (if its there) [message #207791] Thu, 07 December 2006 01:09 Go to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Hi All, I'm a hopeless newbie and I need your help..

I have two tables in which I store details of customers. The main table stores name, address and phone number while the second table stores dates of birth, email IDs and such secondary things. This second table does not have entries for all customers.

Now I want to display customer data in a SELECT statement in the following way -

SELECT customer_id, name, address, phone_no, ... and then

"If a particular customer has record in second table (which can be done by matching CUSTOMER_ID field), then display email address field else display "phone || '@noemail.com'"

For instance, the output should be like -

CUSTOMER_ID NAME ADDRESS PHONE EMAIL
1 Raj Delhi 40055759 raj@yahoo.com
2 Divya Delhi 40055225 40055225@noemail.com

I have no idea how to go about it. If I try to join these tables I get an error or absurd results because not all records can be joined. The main table has 1000 records and second table has only 200.

Thanks a lot for help in anticipation,
Rahul.
Re: How to get data from another table (if its there) [message #207793 is a reply to message #207791] Thu, 07 December 2006 01:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are such a new newbie, you will really benefit from reading a bit of doco rather than us spoonfeeding you the answers.

Search your documentation for OUTER JOIN. Read it. Now search for NVL. These should give you everything you need to solve the problem.

Ross Leishman
Re: How to get data from another table (if its there) [message #208003 is a reply to message #207791] Thu, 07 December 2006 15:15 Go to previous messageGo to next message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
SELECT
t1.customer_id,
t1.name,
t1.phone_no,
NVL(t2.email,t1.customer_id||'@noemail.com')
FROM TABLE1 t1, TABLE2 t2
WHERE t1.customer_id = t2.customer_id(+)
Re: How to get data from another table (if its there) [message #208065 is a reply to message #208003] Fri, 08 December 2006 00:37 Go to previous message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Thanks a lot, both of you.. Razz

Ross, thanks for the general pointer rather than the spoonfeed. I had known the concepts of join but was unwilling to delve into them, it seemed easier to ask. But you are correct. Advice taken.

But the query I came up with after reading up was not as compact as yours, Nixen, so thanks to you too.

Best,
Rahul.
Previous Topic: Dump table data in text files
Next Topic: Lock on Drop User
Goto Forum:
  


Current Time: Fri Dec 09 09:48:24 CST 2016

Total time taken to generate the page: 0.08719 seconds