Home » SQL & PL/SQL » SQL & PL/SQL » Showing duplicates under multiple columns
Showing duplicates under multiple columns [message #233761] Fri, 27 April 2007 06:39 Go to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
I have been trying to work this one out all day and am ready to give up! Thought I'd ask you guys and any help would be greatly appreciated.

I'm attempting to learn my way around oracle and this is what I'm trying to do at the moment:

I have a table for my staff and in that table their name, unique number is included. I then have another table which is called room. Now, each member teaches in a room and I'm trying to work out how to query the table so it shows staff which work in the same room. for example this is how i'm trying to have it look

NAME | NAME | ROOM

john | roe | 394
essie | roe | 259

What I'm having trouble getting my head around is:
How do I select the name and allow it to form two headings? I tried creating two columns however it was just duplicates.

Any help would be greatly appreciated! Cool

[Updated on: Fri, 27 April 2007 06:40]

Report message to a moderator

Re: Showing duplicates under multiple columns [message #233762 is a reply to message #233761] Fri, 27 April 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post:
- Oracle version
- Create statements
- Insert statements
- Output result given the data you provided in previous step

Regards
Michel
Re: Showing duplicates under multiple columns [message #233763 is a reply to message #233761] Fri, 27 April 2007 06:56 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
oops sorry.

Am using oracle 10g

havn't created any queries yet am just bouncing around some ideas in my head how I will go about the problem but am at a dead end.

ttitle "Lecturers sharing Rooms"
COLUMN Name1 FORMAT A15 HEADING "Name"
COLUMN Name2 FORMAT A15 HEADING "Name"
COLUMN room_no FORMAT A15 HEADING "Room_no"
select name as "Name1", name as "Name2", r.room_no
from l, r;


Results :

Fri Apr 27 page 1
Lecturers sharing Rooms

Name Name Room_no
--------------- --------------- ---------------
Goscinski Goscinski GD27
Webb Webb GD27
Garner Garner GD27
Zhang Zhang GD27
Warren Warren GD27
Zhou Zhou GD27
De Paoli De Paoli GD27
Coldwell Coldwell GD27

Fri Apr 27 page 2
Lecturers sharing Rooms

Name Name Room_no
--------------- --------------- ---------------
Silcock Silcock GD27
Newlands Newlands GD27
Horan Horan GD27
Dew Dew GD27
Liu Liu GD27
Padhye Padhye GD27
Goscinski Goscinski LT1
Webb Webb LT1

[Updated on: Fri, 27 April 2007 07:00]

Report message to a moderator

Re: Showing duplicates under multiple columns [message #233786 is a reply to message #233763] Fri, 27 April 2007 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
May I ask you why you don't format the result?
I don't see the create and insert statements.

Regards
Michel
Re: Showing duplicates under multiple columns [message #233808 is a reply to message #233786] Fri, 27 April 2007 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or even a description of the tables (DESC <table_name>) if you don't know how to write CREATE table statements.
Re: Showing duplicates under multiple columns [message #234027 is a reply to message #233761] Sun, 29 April 2007 02:19 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
Sorry.

Tables plus example values

create table l
(staff_no varchar2(3) constraint pk_l_no primary key,
initials varchar2(6),
name varchar2(20),
title varchar2(10));

create table r
(room_no varchar2(5) constraint pk_rno primary key,
room_size number);


create table u
(unit_code varchar2(6) constraint pk_u_code primary key,
name varchar2(30),
staff_no varchar2(3),
room_no varchar2(5),
constraint fk_u foreign key (staff_no) references l(staff_no),
constraint fk_u2 foreign key (room_no) references r(room_no));

insert into l values ('L1', 'A.M.','Goscinski', 'Professor');

insert into r values ('SB420', 200);

insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');


-

If anyone could give me a tip of advice that would be great. I am trying to query the tables so i can find names who both work in the same room and then column it into
NAME | NAME | ROOM

However, I don't understand how to query the data and put them into the two columns? Would this be achieved through using aliases?

So I came up with
COLUMN Name1 FORMAT A15 HEADING "Name"
COLUMN Name2 FORMAT A15 HEADING "Name"
COLUMN room_no FORMAT A15 HEADING "Room_no"
select name as "Name1", name as "Name2", r.room_no
from l, r


Which is incomplete
Re: Showing duplicates under multiple columns [message #234041 is a reply to message #234027] Sun, 29 April 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which one is the first "name"? Which one is the second "name"? Are they the same?

Regards
Michel
Re: Showing duplicates under multiple columns [message #234043 is a reply to message #233761] Sun, 29 April 2007 03:20 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
They are the same
Re: Showing duplicates under multiple columns [message #234044 is a reply to message #234043] Sun, 29 April 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, I don't understand your problem.
Can you explain once again what do you want. Explain it with simple words as if you explain it to your Mom.
With the data you gave what you should be the result.
(btw, one row per table is not really a test case)

Regards
Michel
Re: Showing duplicates under multiple columns [message #234045 is a reply to message #233761] Sun, 29 April 2007 03:36 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
ok.

I want to bring up staff which both teach in the same room. Then show the name next to the name (where both share a relationship by teaching in the same room) then display the room next to it.

Sorry I'm not the best at explaining as you've noticed Laughing
Re: Showing duplicates under multiple columns [message #234046 is a reply to message #234045] Sun, 29 April 2007 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's not just you can't explain clearly.
It is that you don't clearly specify what you want.
There are inconsistencies between all your posts.

At least post a full exemple (formatted with insert statements that we are able to reproduce).

Regards
Michel


Re: Showing duplicates under multiple columns [message #234047 is a reply to message #233761] Sun, 29 April 2007 03:59 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
I'm trying to create the query from scratch based on the table data I have been given. I'm not as such interested in the whole query but more wondering how do I put the names into two columns that use the same room. Thats where I'm stumped, can't find anything in the book about it

insert into l values ('L1', 'A.M.','Goscinski', 'Professor');
insert into l values ('L2', 'G.I.','Webb', 'Professor');
insert into l values ('L3', 'B.J.','Garner', 'Professor');
insert into l values ('L4', 'C.Q.','Zhang', 'Ass. Prof.');
insert into l values ('L5', 'M.','Warren', 'Dr.');
insert into l values ('L6', 'B.B.','Zhou', 'Dr.');
insert into l values ('L7', 'D.','De Paoli', 'Dr.');
insert into l values ('L8', 'J.','Coldwell', 'Dr.'); 
insert into l values ('L9', 'J.J.','Silcock', 'Dr.');
insert into l values ('L10', 'D.A.','Newlands', 'Dr.');
insert into l values ('L11', 'P.','Horan', 'Mr.');
insert into l values ('L12', 'R.A.','Dew', 'Mr.');
insert into l values ('L13', 'L.','Liu', 'Ms.');
insert into l values ('L14', 'P.','Padhye', 'Dr.');


insert into r values ('SB420', 200);
insert into r values ('SC422', 80);
insert into r values ('SC424', 80);
insert into r values ('SC426', 80);
insert into r values ('SC429', 40);
insert into r values ('SB129', 50);
insert into r values ('SB137', 40);
insert into r values ('SB138', 50);
insert into r values ('LT1', 270);
insert into r values ('GD27', 130);
insert into r values ('SD464', 80);

insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');
insert into u values ('SCC104', 'Intro. to Software Dev.', 'L12', 'SB420');
insert into u values ('SCC105', 'Basic Programming Concepts', 'L12', 'SB420');
insert into u values ('SCC107', 'Concepts and Practice for S.E.', 'L11', 'SB420');
insert into u values ('SCC108', 'Database and Info. Retrieval', 'L10', 'SB420');
insert into u values ('SCC109', 'WWW and Internet Programming', 'L5', 'SB420');
insert into u values ('SCC203', 'Object Oriented Programming', 'L12', 'SB138');
insert into u values ('SCC204', 'Data Structures and Algorithms', 'L10', 'SB420');
insert into u values ('SCC208', 'Operating Systems', 'L7', 'SB137');
insert into u values ('SCC209', 'Systems Analysis and Design', 'L5', 'SC422');
insert into u values ('SCC210', 'Advanced Database', 'L4', 'SB137');
insert into u values ('SCC214', 'Applications of A.I.', 'L2', 'SC426');
insert into u values ('SCC217', 'Business Programming Language', 'L12', 'SC426');
insert into u values ('SCC220', 'Computer Architecture', 'L13', 'SC422');
insert into u values ('SCC238', 'Information Systems in Org.', 'L9', 'SC422');
insert into u values ('SCC303', 'Software Engineering', 'L11', 'SB420');
insert into u values ('SCC306', 'Computers, Society, Ethics', 'L8', 'SC424');
insert into u values ('SCC319', 'Computing Project', 'L9', null);
insert into u values ('SCC321', 'Distributed Systems', 'L1', 'SC426');
insert into u values ('SCC333', 'Practical Software Development', 'L11', 'SC424');
insert into u values ('SCC334', 'Electronic Business Systems', 'L5', 'SC426');
insert into u values ('SCC336', 'Computer Networks', 'L1', 'SC422');
insert into u values ('SCC337', 'Data Mining', 'L11', 'SC429');

[Updated on: Sun, 29 April 2007 06:07]

Report message to a moderator

Re: Showing duplicates under multiple columns [message #234050 is a reply to message #234047] Sun, 29 April 2007 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very good and in this case the result should be?

Regards
Michel
Re: Showing duplicates under multiple columns [message #234052 is a reply to message #233761] Sun, 29 April 2007 04:14 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
The result should be listing unique pairs who use a room then column headings should be "Name", "Name", "Room".
Re: Showing duplicates under multiple columns [message #234055 is a reply to message #234052] Sun, 29 April 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the data you gave, what should be the result? Not in words, this time, but the chart, simulate in your mind the result of the query and post it.

Regards
Michel
Re: Showing duplicates under multiple columns [message #234056 is a reply to message #234055] Sun, 29 April 2007 04:28 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
alright as in:

Appleton Dew S375
Brown Dylan S367
Coughlin Webb S376
Isaacson Rambeau S366

and so fourth.
Re: Showing duplicates under multiple columns [message #234057 is a reply to message #234056] Sun, 29 April 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but I don't see any Appleton or Brown or Dylan or Coughlin or Webb or Isaacson or Rambeau in the data you provided.

Regards
Michel
Re: Showing duplicates under multiple columns [message #234058 is a reply to message #233761] Sun, 29 April 2007 04:51 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
No..

I don't either that was from another table (students which is irrelevent on this situation) oops. Its been a long day, shall we try once more hah.

Warren Dew SB420
De Paoli Zhang SB137

Sorry about that, how embarrassing.
Re: Showing duplicates under multiple columns [message #234060 is a reply to message #234058] Sun, 29 April 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now, explain in words how did you come from your data to your result. What was your algorithm, your way?

Regards
Michel
Re: Showing duplicates under multiple columns [message #234063 is a reply to message #233761] Sun, 29 April 2007 05:05 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
I looked up where room_no in the unit code and then for the first line which was
insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');

I checked who else was in SB420 and the first being L12 Dew.

[Updated on: Sun, 29 April 2007 05:07]

Report message to a moderator

Re: Showing duplicates under multiple columns [message #234066 is a reply to message #234063] Sun, 29 April 2007 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but in your example, I have no line inserted into u (but I have line inserted twice in r).

Regards
Michel
Re: Showing duplicates under multiple columns [message #234068 is a reply to message #233761] Sun, 29 April 2007 06:08 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
Post updated
Re: Showing duplicates under multiple columns [message #234165 is a reply to message #234068] Mon, 30 April 2007 04:42 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
To find the number of teachers within one room, you could do the following:
SQL> SELECT COUNT(DISTINCT u.staff_no)
  2        ,u.room_no
  3  FROM   u
  4  GROUP  BY u.room_no
  5  HAVING COUNT(DISTINCT u.staff_no) > 1
  6  /

COUNT(DISTINCTU.STAFF_NO) ROOM_NO
------------------------- -------
                        2 SB137
                        4 SB420
                        4 SC422
                        2 SC424
                        4 SC426


But that brings us back to the example result-set Michel requested. How do you want to show (for example) the 4 teachers that are in room SB420? In your previous posts, you only mention 2 teachers sharing one room, not 4.

My suggestion: put all teachers per room in subsequent rows, something like:

SQL> SELECT DISTINCT sub.room_no
  2                 ,sub.NAME
  3  FROM   (SELECT l.NAME
  4                ,u.room_no
  5                ,COUNT(DISTINCT u.staff_no) over(PARTITION BY u.room_no) numb
  6          FROM   u
  7                ,l
  8          WHERE  l.staff_no = u.staff_no) sub
  9  WHERE  sub.numb > 1
 10  ORDER  BY sub.room_no
 11           ,sub.NAME
 12  /

ROOM_NO NAME
------- --------------------
SB137   De Paoli
SB137   Zhang
SB420   Dew
SB420   Horan
SB420   Newlands
SB420   Warren
SC422   Goscinski
SC422   Liu
SC422   Silcock
SC422   Warren
SC424   Coldwell
SC424   Horan
SC426   Dew
SC426   Goscinski
SC426   Warren
SC426   Webb

16 rows selected


Michel (and others), I hope this post is not considered "spoon feeding". I got intrigued by the whole conversation (thumbs up for Michel's patience!!), got the idea that I got what Wisie was after, so that's why I couldn't resist trying...
Re: Showing duplicates under multiple columns [message #234178 is a reply to message #233761] Mon, 30 April 2007 05:35 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
Haha yes props to Michaels patience.

I have no choice but to put the columns into Name Name Room, that is what has been requested. I don't want you guys to spoonfeed me with it but am just very curious how do I sort the name data into two columns?
Re: Showing duplicates under multiple columns [message #234188 is a reply to message #234178] Mon, 30 April 2007 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But now the question is, as Sabine (skooman) poped it, when you have 4 teachers, how to write them in 2 columns?
Do you want to have all possible combinations of 2 teachers? Or do you have to randomly pick up 2 of them?

Regards
Michel
Re: Showing duplicates under multiple columns [message #234350 is a reply to message #233761] Tue, 01 May 2007 00:55 Go to previous messageGo to next message
wisie
Messages: 14
Registered: April 2007
Junior Member
Thats what I thought too but I'm just going on what I've been asked to do which would be show all possible combinations.
Re: Showing duplicates under multiple columns [message #234353 is a reply to message #234350] Tue, 01 May 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select u.room_no, l.name
  4      from u, l
  5      where l.staff_no = u.staff_no
  6    )
  7  select distinct t1.room_no, t1.name, t2.name
  8  from data t1, data t2
  9  where t2.room_no = t1.room_no
 10    and t2.name > t1.name
 11  order by 1, 2, 3
 12  /
ROOM_ NAME                 NAME
----- -------------------- --------------------
SB137 De Paoli             Zhang
SB420 Dew                  Horan
SB420 Dew                  Newlands
SB420 Dew                  Warren
SB420 Horan                Newlands
SB420 Horan                Warren
SB420 Newlands             Warren
SC422 Goscinski            Liu
SC422 Goscinski            Silcock
SC422 Goscinski            Warren
SC422 Liu                  Silcock
SC422 Liu                  Warren
SC422 Silcock              Warren
SC424 Coldwell             Horan
SC426 Dew                  Goscinski
SC426 Dew                  Warren
SC426 Dew                  Webb
SC426 Goscinski            Warren
SC426 Goscinski            Webb
SC426 Warren               Webb

20 rows selected.

I assumed (name1, name2) is equivalent to (name2,name1) and choose to only display one of them.

Regards
Michel
Re: Showing duplicates under multiple columns [message #235497 is a reply to message #233761] Sun, 06 May 2007 22:42 Go to previous message
wisie
Messages: 14
Registered: April 2007
Junior Member
Sorry about the delayed reply! Michael that is exactly what I was after and I'm extremely greatful.

Previous Topic: Audit_trail=DB
Next Topic: Hi Experts,
Goto Forum:
  


Current Time: Fri Dec 02 16:44:25 CST 2016

Total time taken to generate the page: 0.13000 seconds