Select multiple column values in single query - oracle

Question

I have two tables that store email information:

EMAIL
EMAIL_ADDRESS

EMAIL has:

email ID
timestamp
and other info we don't care about

EMAIL_ADDRESS has:

ID (foreign key references EMAIL.ID)
EMAIL_ADDRESS
TYPE (to, from)

Say I have 6 rows in EMAIL - the query should return the ID, timestamp, to and from address.

At the moment I have this:

SELECT ea.EMAIL_ADDRESS, e.ID, e.sent_date
FROM EMAIL_ADDRESS ea, CHANN_EMAIL e
WHERE e.ID=ea.id
AND ea.TYPE in ('to','from')

This returns 12 rows, in the format: -to, ID, date -from, ID, date

What would the query be so I would have 6 rows with: -to, from, ID, date

Do we have only two rows(one with type 'to' and other with type 'from') in EMAIL_ADDRESS table per EMAIL ID? — Dipendu Paul
– Dipendu Paul, Commented Feb 4, 2014 at 11:42
Yes, your example data is spot on, thanks to all who answered :) — ghost_1989
– ghost_1989, Commented Feb 4, 2014 at 13:08

Reza ArabQaeni · Accepted Answer · 2014-02-04 11:42:36Z

1

You must distinct EMAIL_ADDRESS table to two view:

SELECT eat.EMAIL_ADDRESS as to ,ea.EMAIL_ADDRESS as from, e.ID, e.sent_date
FROM EMAIL_ADDRESS ea, CHANN_EMAIL e,EMAIL_ADDRESS eat
WHERE e.ID=ea.id and e.ID=eat.id
AND ea.TYPE in ('from') AND eat.TYPE in ('to')

answered Feb 4, 2014 at 11:42

Reza ArabQaeni

4,91830 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pulsar · Accepted Answer · 2014-02-04 11:35:19Z

0

try using GROUP BY e.ID or GROUP BY ea.id

answered Feb 4, 2014 at 11:35

pulsar

9951 gold badge9 silver badges22 bronze badges

Comments

Dipendu Paul · Accepted Answer · 2014-02-04 12:12:59Z

Sample Data: email table:

| email_id |     timestamp          |
-------------------------------
| 1        | 2014-02-14 17:30:32.803|
| 2        | 2014-02-24 17:30:32.803|

email_address table:

| id | email_add | type |
-------------------------
| 1  |[email protected]| to   |
| 1  |[email protected]| from |
| 2  |[email protected]| to   |
| 2  |[email protected]| from |

Query:

SELECT tab.email_id, MAX([to]) AS [to], MAX([from]) AS [from], MAX(tab.timestamp) AS [time] FROM
(SELECT e.email_id, 
CASE WHEN type= 'to' THEN ea.email_add ELSE NULL END AS [to],
CASE WHEN type= 'from' THEN ea.email_add ELSE NULL END AS [from], e.timestamp
FROM email e
INNER JOIN email_address ea
ON e.email_id = ea.id) tab
GROUP BY tab.email_id

Result:

|email_id|    to     |  from     |   time                |
----------------------------------------------------------
|   1    |[email protected]|[email protected]|2014-02-14 17:30:32.803|
|   2    |[email protected]|[email protected]|2014-02-24 17:30:32.803|

Collectives™ on Stack Overflow

Select multiple column values in single query - oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related