• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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

0
chaitu chaitu
Asked:
chaitu chaitu
2 Solutions
 
sujith80Commented:
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
sujith80Commented:
>> (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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now