Home » SQL & PL/SQL » SQL & PL/SQL » Help with this "basic" sql. (Oracle 10g)
Help with this "basic" sql. [message #326076] Tue, 10 June 2008 02:15 Go to next message
aegcp
Messages: 8
Registered: May 2008
Junior Member
Hi guys, this is some dummy data to understand my issue easily:

create table employee_definition (def_id number, def_name varchar(50));
insert into employee_definition values (100, 'EMAIL');
insert into employee_definition values (200, 'MOBILE_PHONE');
insert into employee_definition values (300, 'HOME_PHONE');
SQL> select * from employee_definition;

    DEF_ID DEF_NAME
---------- -----------------------------
       100 EMAIL
       200 MOBILE_PHONE
       300 HOME_PHONE

create table employee_data (def_id number, def_value varchar(20), emp_id number);
insert into employee_data values (100, 'test@gmail.com', 123);
insert into employee_data values (200, '01232222', 123);
insert into employee_data values (300, '5555', 123);
insert into employee_data values (100, 'aaaa@gmail.com', 666);
insert into employee_data values (200, '888', 666);
insert into employee_data values (300, '999', 666);
insert into employee_data values (300, '444', 777);

SQL> select * from employee_data;

    DEF_ID DEF_VALUE                EMP_ID
---------- -------------------- ----------
       100 test@gmail.com              123
       200 01232222                    123
       300 5555                        123
       100 aaaa@gmail.com              666
       200 888                         666
       300 999                         666
       300 999                         777
7 rows selected.


Both tables are joined by def_id. I'm supposed to create a SQL that will return me the email, mobile_phone, and home_phone for a set of employees. The result will be something like this:

EMPLOYEE ID | HOME_PHONE | MOBILE_PHONE | EMAIL
123         |  5555  |    01232222      | test@gmail.com
666         |  999  |    888      | aaaa@gmail.com
777         |  444  |    null     | null


The thing I'm finding difficulty here is that the same column is used to store different values, based on the value in employee_definition table (something like a key/value pair). If I do:
SQL> select emp_id, def_value as email from employee_data, employee_definition
  2  where employee_data.def_id = employee_definition.def_id
  3  and employee_definition.def_name = 'EMAIL';
 
    EMP_ID EMAIL
---------- --------------------
       123 test@gmail.com
       666 aaaa@gmail.com


It's partially ok.. I'm just getting the definition for 'EMAIL'. But how can I get all the values in a single query (including null if there isn't any), knowing that the column stores different values based on def_name?
Re: Help with this "basic" sql. [message #326084 is a reply to message #326076] Tue, 10 June 2008 02:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try something like this - you just need multiple copies of the same tables:
select ed1.emp_id,
       ed1.def_value as email, 
       ed2.def_value as home_phone,
       ed3.def_value as mobile_phone,
from   employee_data ed1,
       employee_data ed2,
       employee_data ed3,
       employee_definition def1,
       employee_definition def2,
       employee_definition def3,
where  123 = ed1.def_id
and    ed1.def_id = ed2.def_id
and    ed1.def_id = ed3.def_id
and    ed1.def_id = def1.def_id
and    ed2.def_id = def2.def_id
and    ed3.def_id = def3.def_id
and    def1.def_name = 'EMAIL'
and    def2.def_name = 'HOME_PHONE'
and    def3.def_name = 'MOBILE_PHONE'
Re: Help with this "basic" sql. [message #326112 is a reply to message #326084] Tue, 10 June 2008 04:23 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
The above query is not working ,It's returning empty row.
Re: Help with this "basic" sql. [message #326114 is a reply to message #326112] Tue, 10 June 2008 04:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well 0/10 for doing your own debugging, but only 2/10 for me for not testing the code.

select ed1.emp_id,
       ed1.def_value as email, 
       ed2.def_value as home_phone,
       ed3.def_value as mobile_phone
from   employee_data ed1,
       employee_data ed2,
       employee_data ed3,
       employee_definition def1,
       employee_definition def2,
       employee_definition def3
where  123 = ed1.emp_id
and    ed1.emp_id = ed2.emp_id
and    ed1.emp_id = ed3.emp_id
and    ed1.def_id = def1.def_id
and    ed2.def_id = def2.def_id
and    ed3.def_id = def3.def_id
and    def1.def_name = 'EMAIL'
and    def2.def_name = 'HOME_PHONE'
and    def3.def_name = 'MOBILE_PHONE';
Re: Help with this "basic" sql. [message #326181 is a reply to message #326076] Tue, 10 June 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
aegcp wrote on Tue, 10 June 2008 03:15

create table employee_definition (def_id number, def_name varchar(50));



Proper use would be VARCHAR2, not VARCHAR.
Re: Help with this "basic" sql. [message #326296 is a reply to message #326076] Wed, 11 June 2008 00:17 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

its not done by me. ..
i tried but get error since not using max function. . .


the following query is working fine for your above qn.. .

SELECT emp_id, MAX (email) email, MAX (mobile_phone) mobile_phone,
MAX (home_phone) home_phone
FROM (SELECT emp_id, DECODE (def_name, 'EMAIL', def_value, NULL) email,
DECODE (def_name,
'MOBILE_PHONE', def_value,
NULL
) mobile_phone,
DECODE (def_name, 'HOME_PHONE', def_value, NULL) home_phone
FROM employee_data a, employee_definition b
WHERE a.def_id = b.def_id)
GROUP BY emp_id

[mod-edit] color and font removed

[Updated on: Wed, 11 June 2008 07:50] by Moderator

Report message to a moderator

Re: Help with this "basic" sql. [message #326297 is a reply to message #326076] Wed, 11 June 2008 00:21 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Pls let me know if any body got the solution for this.

I tried with the following query


select emp_id,decode(def_id,100,DEF_VALUE) as EMAIL ,
decode(def_id,200,DEF_VALUE) as MOBILE_PHONE ,
decode(def_id,300,DEF_VALUE) as HOME_PHONE
from employee_data

and got the o/p as

EMP_ID EMAIL MOBILE_PHONE HOME_PHONE
123.00 test@gmail.com
123.00 01232222
123.00 5555
666.00 aaaa@gmail.com
666.00 888
666.00 999
777.00 444

but how can i make the rows merged ???...i mean emp id with 123 hav to return 1 single row.
Re: Help with this "basic" sql. [message #326298 is a reply to message #326296] Wed, 11 June 2008 00:26 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Thanks Syed Good work
Re: Help with this "basic" sql. [message #326303 is a reply to message #326298] Wed, 11 June 2008 00:44 Go to previous messageGo to next message
aegcp
Messages: 8
Registered: May 2008
Junior Member
I had to use my base employee table in order to get the results the way I wanted. Couldn't do it without it.

Don't fully understand the solution seyed456. For example, why use MAX(email)?


create table employee_test(emp_id number, name varchar(40));
insert into employee_test values(123, 'john');
insert into employee_test values(666, 'Andrew');
insert into employee_test values(777, 'Phil');


here's an attempt:

SQL> SELECT e.emp_id,
  2     e_phone.def_value as home_phone,
  3     e_mobile.def_value as mobile,
  4     e_email.def_value as email
  5  FROM employee_test e,
  6  (select employee_data.*
  7          from employee_data, employee_definition
  8          where employee_data.def_id = employee_definition.def_id
  9          and employee_definition.def_name = 'EMAIL') e_email,
 10  (select employee_data.*
 11          from employee_data, employee_definition
 12          where employee_data.def_id = employee_definition.def_id
 13          and employee_definition.def_name = 'MOBILE_PHONE') e_mobile,
 14  (select employee_data.*
 15          from employee_data, employee_definition
 16          where employee_data.def_id = employee_definition.def_id
 17          and employee_definition.def_name = 'HOME_PHONE') e_phone
 18  WHERE e_email.emp_id (+) = e.emp_id
 19  AND e_mobile.emp_id (+) = e.emp_id
 20  AND e_phone.emp_id (+) = e.emp_id
 21  order by e.emp_id
 22  ;

    EMP_ID HOME_PHONE           MOBILE               EMAIL
---------- -------------------- -------------------- --------------------
       123 5555                 01232222             test@gmail.com
       666 999                  888                  aaaa@gmail.com
       777 444
Re: Help with this "basic" sql. [message #326307 is a reply to message #326296] Wed, 11 June 2008 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
seyed456 wrote on Wed, 11 June 2008 07:17
its not done by me. ..
i tried but get error since not using max function. . .


the following query is working fine for your above qn.. .


SELECT emp_id, MAX (email) email, MAX (mobile_phone) mobile_phone,
MAX (home_phone) home_phone
FROM (SELECT emp_id, DECODE (def_name, 'EMAIL', def_value, NULL) email,
DECODE (def_name,
'MOBILE_PHONE', def_value,
NULL
) mobile_phone,
DECODE (def_name, 'HOME_PHONE', def_value, NULL) home_phone
FROM employee_data a, employee_definition b
WHERE a.def_id = b.def_id)
GROUP BY emp_id


seyed456, as the forum guidelines clearly state, code is to be enclosed by [code] tags, NOT [color]
Making your code blue adds nothing to the readability.
Re: Help with this "basic" sql. [message #326309 is a reply to message #326076] Wed, 11 June 2008 01:05 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
select d.emp_id,
       max(case def_name
             when 'HOME_PHONE' then
              def_value
           end) home_phone,
       max(case def_name
             when 'MOBILE_PHONE' then
              def_value
           end) mobile_phone,
       max(case def_name
             when 'EMAIL' then
              def_value
           end) email
  from employee_data d
 inner join employee_definition t on d.def_id = t.def_id
 group by d.emp_id
 order by emp_id


[Mod-edit: Frank added code tags]

[Updated on: Wed, 11 June 2008 01:05] by Moderator

Report message to a moderator

Re: Help with this "basic" sql. [message #326310 is a reply to message #326309] Wed, 11 June 2008 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi cnvegnix, welcome to the forum.
As in many code-related forums we have some guidelines on how to post, in order to keep the posted code readable.
As you can see, I changed your post and added the necessary tags to let the code stand out as such, and to have it displayed conserving format.
If you would take a look at our forum guidelines, I am sure you will find some other useful rules-of the house!
Re: Help with this "basic" sql. [message #326318 is a reply to message #326076] Wed, 11 June 2008 01:43 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

u can also use this ..

SELECT   emp_id,MAX( DECODE (def_name, 'EMAIL', def_value, NULL)) email,
                MAX(DECODE (def_name,
                         'MOBILE_PHONE', def_value,
                         NULL
                        )) mobile_phone,
                 max(DECODE (def_name, 'HOME_PHONE', def_value, NULL)) home_phone
            FROM employee_data a, employee_definition b
           WHERE a.def_id = b.def_id
GROUP BY emp_id
Re: Help with this "basic" sql. [message #326328 is a reply to message #326076] Wed, 11 June 2008 01:56 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you not happy with the number of answers posted in this link or you are too busy to find a solution by yourself.

http://forums.oracle.com/forums/thread.jspa?threadID=667512&start=0&tstart=15

Regards

Raj
Previous Topic: What is the differences between views and synonyms?
Next Topic: Outer Join Problem
Goto Forum:
  


Current Time: Sun Dec 04 13:01:36 CST 2016

Total time taken to generate the page: 0.04443 seconds