Home » SQL & PL/SQL » SQL & PL/SQL » Alternate way other then UNION command
Alternate way other then UNION command [message #245323] Sat, 16 June 2007 00:21 Go to next message
jimm88
Messages: 2
Registered: June 2007
Junior Member
hi, I need help here, I have try my best to create the result set for the SQL statement below, but i just don't get it right Frown. What I want is to use an alternate method other then UNION?

This is the query...
SELECT role.role_id, role.role_name, role.description
  FROM role 
 INNER JOIN User_Right ON User_Right.role_Id= role.role_id 
 INNER JOIN Func_Right ON role.role_id = Func_Right.role_id 
 WHERE User_Right.emp_id=100009 
   AND Func_Right.func_id=00000001198
 UNION 
SELECT role.role_id, role.role_name, role.description
  FROM role 
 INNER JOIN User_Right ON User_Right.role_Id= role.role_id 
 WHERE User_Right.emp_id=100009 
   AND User_Right.role_id=0 ORDER BY role.role_id


These are the creation scripts for the sql statement above
CREATE TABLE ROLE
(role_id		number,
 role_name	varchar2(20),
 description  varchar2(50));
INSERT INTO ROLE VALUES (0, 'Admin', 'Administrator');
INSERT INTO ROLE VALUES (1000, 'HR', 'Human Resource');
INSERT INTO ROLE VALUES (1001, 'ACC', 'Accounts');
INSERT INTO ROLE VALUES (1002, 'PUR', 'Purchase Agent');
INSERT INTO ROLE VALUES (1003, 'FIN', 'Finance');

CREATE TABLE User_Right
( emp_id		number,
  role_id	number,
  role_lvl	number,
  department  varchar2(20));

INSERT INTO User_Right VALUES (100009, 0, 0,'');
INSERT INTO User_Right VALUES (100009, 1000, 0,'');
INSERT INTO User_Right VALUES (100009, 1001, 0,'');
INSERT INTO User_Right VALUES (100009, 1002, 0,'');
INSERT INTO User_Right VALUES (100009, 1003, 0,'');

CREATE TABLE Func_Right
(
	role_id number,
   func_id number,
   department varchar2(20)
);

INSERT INTO Func_Right VALUES (1000, 00000001198, '');
INSERT INTO Func_Right VALUES (1001, 00000001198, '');
INSERT INTO Func_Right VALUES (1002, 00000001198, '');
INSERT INTO Func_Right VALUES (1003, 00000001198, '');


Can anyone help me with this...
help,
Jim

[Updated on: Sat, 16 June 2007 00:28]

Report message to a moderator

Re: Alternate way other then UNION command [message #245332 is a reply to message #245323] Sat, 16 June 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What I want is to use an alternate method other then UNION?

Why?
Maybe it is the best way to achieve your needs.

Regards
Michel

[Updated on: Sat, 16 June 2007 01:20]

Report message to a moderator

Re: Alternate way other then UNION command [message #245375 is a reply to message #245332] Sat, 16 June 2007 12:01 Go to previous messageGo to next message
jimm88
Messages: 2
Registered: June 2007
Junior Member
thanks for your reply Smile

this is for my own practises and additional knowledge, that is why I wanna try it out with another method. But i couldn't reproduce the same record set...

hope you can assist me on this...
Re: Alternate way other then UNION command [message #245377 is a reply to message #245323] Sat, 16 June 2007 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT role.role_id, role.role_name, role.description
  FROM role 
 INNER JOIN User_Right ON User_Right.role_Id= role.role_id 
 INNER JOIN Func_Right ON role.role_id = Func_Right.role_id 
 WHERE (User_Right.emp_id=100009 
   AND Func_Right.func_id=00000001198)
OR
 (User_Right.emp_id=100009 
   AND User_Right.role_id=0 ORDER BY role.role_id)
Re: Alternate way other then UNION command [message #245378 is a reply to message #245375] Sat, 16 June 2007 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't use the same tables.
You don't restrict on the same conditions.
These are 2 distinct queries.
I don't know if it is possible to warp SQL to avoid union, I don't even try to think a second about it, it is a useless exercise, it'd be a wrong use of SQL.

Regards
Michel

[Updated on: Sat, 16 June 2007 12:08]

Report message to a moderator

Re: Alternate way other then UNION command [message #245381 is a reply to message #245377] Sat, 16 June 2007 12:24 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even after fixing the misplaced last parenthesis, this will not work because of the join with Func_Right.
So role_id that satisfies the first query but does not exist in Func_Right will not appear in your query.
You have to outer join Func_Right.
But as I previously said this will be an error in real case. Often we do the opposite (split queries with union [all]) to limit outer join flaw.

Regards
Michel
Previous Topic: how to write PL/SQL for permutation..?
Next Topic: oracle select results in excelsheet
Goto Forum:
  


Current Time: Sun Dec 11 00:25:24 CST 2016

Total time taken to generate the page: 0.08457 seconds