how to better the below query

I want to check whether employees(1,2) country's same or not.if same following query will fetch true otherwise nothing will come.below query is working as expected.i suspect that we can  better that query.why i suspect that do i need to pass values in subquery also? am i right or wrong?any other way writing the same query?i think using exists also we can write this query.

SELECT distinct 'true' FROM EMPLOYEE EMP 
WHERE 
ID IN(1,2)  AND
1 = ( SELECT COUNT(DISTINCT COUNTRY) 
FROM  EMPLOYEE_PACKS EMPK 
WHERE EMPK.EMP_ID IN(1,2) 
);
 
CREATE TABLE EMPLOYEE
(
  ID			NUMBER NOT NULL ,
  EMPNO          VARCHAR2(10)
 
);
 
alter table EMPLOYEE
  add constraint EMP_PK primary key (ID);
  
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(1,'EMP1');
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(2,'EMP2');
INSERT INTO EMPLOYEE(ID,EMPNO) VALUES(3,'EMP3');
 
create table EMPLOYEE_PACKS
(
  ID     NUMBER not null,
  EMP_ID NUMBER,
  COUNTRY              VARCHAR2(20)    
);
 
alter table EMPLOYEE_PACKS
  add constraint EMPCS_PK primary key (ID);
 
alter table EMPLOYEE_PACKS
  add constraint EMPCS_FK foreign key (EMP_ID)
  references EMPLOYEE (ID);
 
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID,COUNTRY) VALUES(1,1,'A');
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID,COUNTRY) VALUES(2,1,'A');
INSERT INTO EMPLOYEE_PACKS(ID,EMP_ID,COUNTRY) VALUES(3,1,'A');
 
INSERT INTO EMPLOYEE_DOCS(ID,EMP_ID,COUNTRY) VALUES(4,2,'B');
INSERT INTO EMPLOYEE_DOCS(ID,EMP_ID,COUNTRY) VALUES(5,2,'B');
INSERT INTO EMPLOYEE_DOCS(ID,EMP_ID,COUNTRY) VALUES(6,1,'A');
INSERT INTO EMPLOYEE_DOCS(ID,EMP_ID,COUNTRY) VALUES(7,3,'C');
INSERT INTO EMPLOYEE_DOCS(ID,EMP_ID,COUNTRY) VALUES(8,3,'C');

Open in new window

LVL 20
chaitu chaituAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Your query does not return the correct results.

It doesnt check whether both employees are present in employee_Packs or not.
You can do it like this.
SQL> select * from EMPLOYEE;
 
        ID EMPNO
---------- ----------
         1 EMP1
         2 EMP2
         3 EMP3
 
SQL> select * from EMPLOYEE_PACKS;
 
        ID     EMP_ID COUNTRY
---------- ---------- --------------------
         1          1 A
         2          1 A
         3          2 A
 
SQL> select case when ce > 1 and cc=1 then 'true' else 'false' end status
  2  from (
  3  select count(distinct empk.emp_id) ce, count(distinct country) cc
  4  from EMPLOYEE EMP , EMPLOYEE_PACKS EMPK 
  5  where emp.id = empk.emp_id
  6  and emp.ID IN(1,2) 
  7  );
 
STATU
-----
true
 
SQL> update EMPLOYEE_PACKS set emp_id = 1 where id = 3;
 
1 row updated.
 
SQL> select case when ce > 1 and cc=1 then 'true' else 'false' end status
  2  from (
  3  select count(distinct empk.emp_id) ce, count(distinct country) cc
  4  from EMPLOYEE EMP , EMPLOYEE_PACKS EMPK 
  5  where emp.id = empk.emp_id
  6  and emp.ID IN(1,2) 
  7  );
 
STATU
-----
false
 
SQL> 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MilleniumaireCommented:
Chaituu,  the code you have written could be simplified further as follows:

SELECT 'true' FROM SYS.DUAL
WHERE
1 = ( SELECT COUNT(DISTINCT COUNTRY)
FROM  EMPLOYEE_PACKS EMPK
WHERE EMPK.EMP_ID IN(1,2)
);

Note: SYS.DUAL should only ever contain a single row.

However, as sujith80 points out, the above code does not take into consideration whether bot employees exist in the EMPLOYEE_PACKS table.

You don't make it clear if this is a one-off piece of code or if the (1,2) values are to be passed in as parameters.  Is it possible that you could receive a larger list of employee id's or will it always be only two?
0
chaitu chaituAuthor Commented:
Is it possible that you could receive a larger list of employee id's or will it always be only two?
i can pass larger list of employee id's ;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaitu chaituAuthor Commented:
sujit,

It doesnt check whether both employees are present in employee_Packs or not.
you are absoluetly correct .yes i agree that.

without using case statment(i dont want boolean) if both countries are same then display only employee information  other wise don't display anything.with the below its not possible.how to modify this query?

SELECT *  FROM EMPLOYEE EMP
WHERE
ID IN(1,2)  AND
1 = ( SELECT COUNT(DISTINCT COUNTRY)
FROM  EMPLOYEE_PACKS EMPK
WHERE
emp.id = empk.emp_id
);
0
SujithData ArchitectCommented:
>> (i dont want boolean)
It is not boolean. It is just a string.

FYI. an sql query can never return boolean.

Your query is not efficient.
You may try the below one. You may select anything you need from the query. 'true' or 'yes' or whatever..
SQL> select * from employee1;
 
        ID EMPNO
---------- ----------
         1 EMP1
         2 EMP2
         3 EMP3
 
SQL> select * from employee1_packs;
 
        ID     EMP_ID COUNTRY
---------- ---------- --------------------
         1          1 A
         2          1 A
         3          1 A
 
SQL> select 'whatever'
  2  from (
  3      select count(distinct empk.emp_id) ce, count(distinct country) cc
  4      from EMPLOYEE1 EMP , EMPLOYEE1_PACKS EMPK
  5      where emp.id = empk.emp_id
  6      and emp.ID IN(1,2)
  7      )
  8  where ce > 1 and cc=1 ;
 
no rows selected
 
SQL> update employee1_packs set emp_id = 2 where id = 3;
 
1 row updated.
 
SQL> select * from employee1_packs;
 
        ID     EMP_ID COUNTRY
---------- ---------- --------------------
         1          1 A
         2          1 A
         3          2 A
 
SQL> select 'whatever'
  2  from (
  3      select count(distinct empk.emp_id) ce, count(distinct country) cc
  4      from EMPLOYEE1 EMP , EMPLOYEE1_PACKS EMPK
  5      where emp.id = empk.emp_id
  6      and emp.ID IN(1,2)
  7      )
  8  where ce > 1 and cc=1 ;
 
'WHATEVE
--------
whatever
 
SQL>

Open in new window

0
awking00Commented:
See attached.
true.txt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.