| How do i compare values from 2 columns in 1 table? [message #3996] |
Thu, 31 October 2002 19:18  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
I have a problem that i have been trying to solve and although i have come up with the correct solution, i feel that i have gone about it incorrectly. Can you help and explain the most efficient solution?
As you can see, there are four rows in the table. Client_id 1503, 1504 and 1505 have a referance to a parent of 1501, 1501 and 1500.
I need to list all parties involved where a smaller group is part of an orchestra.(eg. Sydney Symphony Orchestra, Melbourne Symphony Orchestra, Southern cross String Quartet, Barcaldine Baaarbershop Quartet and Blue Room Quintet)
Client_ ID Name Parent Client_type
1500 Sydney Symphony Orchestra T
1501 Melbourne Symphony Orchestra T
1502 Dave”Ēs Didjeridoo Duo P
1503 Southern Cross String Quartet 1501 P
1504 Barcaldine Barbershop Quartet 1501 P
1505 Blue Room Quintet 1500 P
1506 Wallerawang Whistlers P
Here is the code i have used. For my solution i have created a view to help me. Is there a more efficient method?
CREATE TABLE clients
(
client_id varchar2(4),
name varchar2(30),
parent varchar2(4),
client_type varchar2(1),
PRIMARY KEY (client_id),
CONSTRAINT fk_c_clients
FOREIGN KEY (parent)
REFERENCES clients(client_id)
on delete cascade
);
insert into clients values
('1500', 'Sydney Symphony Orchestra', NULL, 'T');
insert into clients values
('1501', 'Melbourne Symphony Orchestra', NULL, 'T');
insert into clients values
('1502', 'Daves Didjeridoo Duo', NULL, 'P');
insert into clients values
('1503', 'Southern Cross String Quartet', '1501', 'P');
insert into clients values
('1504', 'Barcaldine Barbershop Quartet', '1501', 'P');
insert into clients values
('1505', 'Blue Room Quartet', '1500', 'P');
insert into clients values
('1506', 'Wallerawang Whistlers', NULL, 'P');
CREATE or replace VIEW myview (par) as
SELECT parent
FROM clients
where parent is not null;
SELECT name
FROM clients c
WHERE parent is not null
UNION
SELECT name
FROM myview m, clients c
WHERE m.par = c.client_id;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
