Home » SQL & PL/SQL » SQL & PL/SQL » Select within a select?
Select within a select? [message #121917] Wed, 01 June 2005 14:36 Go to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Hi.

The following is a very basic example of what I'd like to do:

Select s.first, s.middle, s.last, (select decode(c.code, null, 'No Math','MAT', 'Math', 'No Math') from classes c where c.code='MAT') from students s

However, I'd actually like the nested select to be specific to the student. The CLASSES table has many rows for each student. I got an error when trying to link the tables by their common column (c.idnumber=s.idnumber).

Is this even possible?

My goal is for the result set to have every student and information as to whether they took a certain class which is why I can't do a subquery in the where clause.

Thanks for your help!
Re: Select within a select? [message #121918 is a reply to message #121917] Wed, 01 June 2005 14:44 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
?????

select s.first, s.middle, s.last, c.code from classes c, students s
where c.code='MAT' and
c.idnumber=s.idnumber
Re: Select within a select? [message #121919 is a reply to message #121918] Wed, 01 June 2005 14:56 Go to previous messageGo to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Thanks but that won't work because I need all students and not all of them have taken the class. This is not the actual query so I can't just get those students who took Math. It is very simplified so I need all of the students to show up regardless of this one criteria because of the other information that the bigger query will have. Thanks though.
Re: Select within a select? [message #121920 is a reply to message #121917] Wed, 01 June 2005 15:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I must admit I'm pretty lost as to what you mean, but are you aware of outer joins and would they apply to your situation?
Re: Select within a select? [message #121928 is a reply to message #121917] Wed, 01 June 2005 16:11 Go to previous messageGo to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
I apologize for being unclear. I guess I failed miserably at simplifying the matter. I'm actually modifying a view and wanted to add a field to the view that would be drawn from a "child" table where each person has several different records and may not have the record that I am looking for. No matter what the situation each record in the "parent" table must be displayed. In the case of the person having the appropriate record in the child table, I'd want the new view field to display the value in the child table . In the case of the person not having the appropriate record (but very likely having other records in the table), I'd want the new view field to be null. Outer joins would preserve the parent table records but how would I suppress the unwanted child records from displaying? I didn't want to put it in the main where clause because I really don't want to filter the data set any further. I hope I'm a bit clearer.
Re: Select within a select? [message #121932 is a reply to message #121917] Wed, 01 June 2005 16:48 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ok, I think I'm with you a bit more, correct me if I'm off target, but if you have multiple possible child records that satisfy the condition, how is it you plan to determine which of those multiple records should appear with the parent?

I mean fundamentally you either have to filter them down from multiple to one. Or use criteria to elminate all but one. Know what I mean?

You are saying, for a given parent, there could be 0, 1, or many. If there are 0 you want to show null. That is fine, that is easy, just an outer join. If there is one, again that is easy, the outer join.

But if there are more than one, then you will get more than one, unless you do something to make it so there aren't more than one but only one. In other words you've got to be applying some sort of condition to your situation.

I think though, if I'm on the right page, what you want to use is an inline view and an outer join.
Re: Select within a select? [message #121935 is a reply to message #121917] Wed, 01 June 2005 16:56 Go to previous messageGo to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
Thanks for working through this with me! You've gotten the main gist. This is why I thought a select within a select might help me but I don't know how to "link" that selection with the parent idnumber.

Going back to my simplistic example where the STUDENT table is the parent table and the CLASSES table is the child table and each student has taken many different classes but may or may not have taken math, I'd like to attempt to do the following:

Select s.idnumber, s.first, s.middle, s.last, (select decode(c.code, null, 'No Math','MATH', 'Math', 'No Math') from classes c where c.code='MATH' and c.idnumber=s.idnumber) from students s

Also, each student will only have taken each class once so there would only be one MATH record if it exists.

This doesn't work because "s.idnumber" hasn't been established yet. Is this doable in another way?
Re: Select within a select? [message #121941 is a reply to message #121917] Wed, 01 June 2005 17:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ok, here is one way. Basically it is just moving your query from the select clause to the from clause (called an inline view). I'm sure there are other methods to solve the problem, but I find inline views to be very useful:

MYDBA@ORCL > 
MYDBA@ORCL > create table student (sid number not null, name varchar2(30) not null);

Table created.

MYDBA@ORCL > 
MYDBA@ORCL > create table class (cid number not null, sid number not null, name varchar2(30) not null);

Table created.

MYDBA@ORCL > 
MYDBA@ORCL > insert into student values (1, 'Bob');

1 row created.

MYDBA@ORCL > insert into student values (2, 'Phil');

1 row created.

MYDBA@ORCL > insert into student values (3, 'John');

1 row created.

MYDBA@ORCL > insert into student values (4, 'Allen');

1 row created.

MYDBA@ORCL > 
MYDBA@ORCL > insert into class values (1, 1, 'Biology');

1 row created.

MYDBA@ORCL > insert into class values (2, 1, 'English');

1 row created.

MYDBA@ORCL > insert into class values (3, 1, 'Math');

1 row created.

MYDBA@ORCL > insert into class values (4, 2, 'Biology');

1 row created.

MYDBA@ORCL > insert into class values (5, 2, 'English');

1 row created.

MYDBA@ORCL > insert into class values (6, 2, 'Physics');

1 row created.

MYDBA@ORCL > insert into class values (7, 3, 'Math');

1 row created.

MYDBA@ORCL > 
MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > 
MYDBA@ORCL > 
MYDBA@ORCL > select student.name, nvl(c.name, 'No Math')
  2  from student, (select sid, name from class where name = 'Math') c
  3  where student.sid = c.sid (+)
  4  order by student.name;

NAME                           NVL(C.NAME,'NOMATH')
------------------------------ ------------------------------
Allen                          No Math
Bob                            Math
John                           Math
Phil                           No Math

4 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > drop table student;

Table dropped.

MYDBA@ORCL > drop table class;

Table dropped.

MYDBA@ORCL > 
MYDBA@ORCL > spool off;


But of course if one person can have more than one math class then they will be listed more than once.

Gotta run, and hope it helps.
Re: Select within a select? [message #121947 is a reply to message #121917] Wed, 01 June 2005 18:45 Go to previous messageGo to next message
khmelen
Messages: 16
Registered: June 2005
Junior Member
This is fantastic!! Thanks so very much!!!!
Re: Select within a select? [message #409802 is a reply to message #121941] Wed, 24 June 2009 02:30 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Thanks for the tips. I was able to use this example and customized to my needs.

I would appreciate if any one can provide feedback for the following query. is it workable or needs to be simplified?

background info:

The following tables are joined via se_contact_id. For each contact in se_contact table, there are potentially multiple address,email and phone. I want to get everything regardless if any of the address,email and phone are null.

Thanks,

Tom


---tables--------

se_contact
se_contact_email
se_contact_phone
se_contact_address

=== lookup tables for contact type,position type (personnel), email,address,phone---

se_contact_type_lookup
personnel_type_lookup
email_type_lookup
phone_type_lookup
address_type_lookup



select 
se_contact_type_desc,
personnel_type_name,
se_contact.se_first_name,
se_contact.se_last_name,
a.se_address1,
a.se_address2,
a.se_city,
a.state_id,
a.se_zipcode,
nvl(p.phone_type,'No Label')Type ,
nvl( p.se_phone_num,'No Phone')Phone ,
nvl(p.se_phone_ext,'No Extension')Extension,
nvl(e.email_type_desc,'N/A')Type ,
nvl(e.se_email_addr,'No Email') Email
from se_contact, 
(select se_contact_id, phone_type, se_phone_num,se_phone_ext from
se_contact_phone x, phone_type_lookup l where x.phone_type_num = l.phone_type_num)p,
se_contact_address a,op_contacts s,
(select se_contact_id,email_type_desc,i.se_email_addr from se_contact_email i, email_type_lookup u
where i.email_type_id = u.email_type_id)e,
personnel_type_lookup per,
se_contact_type_lookup secup
where 
se_contact.se_contact_id = p.se_contact_id(+) and
se_contact.se_contact_id = a.se_contact_id(+) and
se_contact.se_contact_id = s.se_contact_id(+) and
se_contact.se_contact_id = e.se_contact_id(+) and
se_contact.personnel_type_id = per.personnel_type_id and
se_contact.se_contact_type_id = secup.se_contact_type_id and
order by se_contact.se_last_name 



[Updated on: Wed, 24 June 2009 03:10]

Report message to a moderator

Re: Select within a select? [message #409807 is a reply to message #121917] Wed, 24 June 2009 02:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the error that you get when you run this (actual error code plus message please) as this syntax is fine

Select s.idnumber
      ,s.first
      ,s.middle
      ,s.last
      ,(select decode(c.code, null, 'No Math','MATH', 'Math', 'No Math') 
        from   classes c 
        where c.code='MATH' 
        and c.idnumber=s.idnumber)
from  students s



Your logic in the inner query is a little messed up too - it should be:
,NVL((select decode(c.code, 'MATH', 'Math') 
               from   classes c 
               where c.code='MATH' 
               and c.idnumber=s.idnumber),'No Math')
from  students s
as the subquery will only ever return a row with code ='MATH', or return no rows.
Re: Select within a select? [message #409917 is a reply to message #121917] Wed, 24 June 2009 07:52 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just stumbled back here during a rare chance to browse the forums lately, and this topic was brought back to the top due to the followup question earlier today. In re-reading the thread, I noticed that I now understand what the OP in 2005 was trying to do. So I've expanded the example I did to show three different ways to answer the same question. As with everything, the one that is the best is dependent on your situation, but I'd consider all three to be good techniques to know. (not that these are the only 3 methods by any means).

MYDBA@orcl > create table student (sid number not null, name varchar2(30) not null);

Table created.

MYDBA@orcl > create table class (cid number not null, sid number not null, name varchar2(30) not null);

Table created.

MYDBA@orcl >
MYDBA@orcl > insert into student values (1, 'Bob');

1 row created.

MYDBA@orcl > insert into student values (2, 'Phil');

1 row created.

MYDBA@orcl > insert into student values (3, 'John');

1 row created.

MYDBA@orcl > insert into student values (4, 'Allen');

1 row created.

MYDBA@orcl >
MYDBA@orcl > insert into class values (1, 1, 'Biology');

1 row created.

MYDBA@orcl > insert into class values (2, 1, 'English');

1 row created.

MYDBA@orcl > insert into class values (3, 1, 'Math');

1 row created.

MYDBA@orcl > insert into class values (4, 2, 'Biology');

1 row created.

MYDBA@orcl > insert into class values (5, 2, 'English');

1 row created.

MYDBA@orcl > insert into class values (6, 2, 'Physics');

1 row created.

MYDBA@orcl > insert into class values (7, 3, 'Math');

1 row created.

MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl >
MYDBA@orcl > select * from student order by sid;

       SID NAME
---------- -------------------------------------
         1 Bob
         2 Phil
         3 John
         4 Allen

4 rows selected.

MYDBA@orcl > select * from class order by cid;

       CID        SID NAME
---------- ---------- -------------------------------------
         1          1 Biology
         2          1 English
         3          1 Math
         4          2 Biology
         5          2 English
         6          2 Physics
         7          3 Math

7 rows selected.

MYDBA@orcl >
MYDBA@orcl > select student.name, nvl(c.name, 'No Math')
  2  from student, (select sid, name from class where name = 'Math') c
  3  where student.sid = c.sid (+)
  4  order by student.name;

NAME                                  NVL(C.NAME,'NOMATH')
------------------------------------- ------------------------------
Allen                                 No Math
Bob                                   Math
John                                  Math
Phil                                  No Math

4 rows selected.

MYDBA@orcl >
MYDBA@orcl > select
  2          student.name,
  3          nvl((select class.name
  4                  from class where student.sid = class.sid and class.name = 'Math')
  5                  ,'No Math')
  6  from student
  7  order by student.name;

NAME                                  NVL((SELECTCLASS.NAMEFROMCLASS
------------------------------------- ------------------------------
Allen                                 No Math
Bob                                   Math
John                                  Math
Phil                                  No Math

4 rows selected.

MYDBA@orcl >
MYDBA@orcl > select student.name, nvl(class.name, 'No Math')
  2  from student, class
  3  where student.sid = class.sid (+)
  4  and class.name (+) = 'Math'
  5  order by student.name;

NAME                                  NVL(CLASS.NAME,'NOMATH')
------------------------------------- ------------------------------
Allen                                 No Math
Bob                                   Math
John                                  Math
Phil                                  No Math

4 rows selected.

MYDBA@orcl >
MYDBA@orcl > drop table student;

Table dropped.

MYDBA@orcl > drop table class;

Table dropped.

MYDBA@orcl > set echo off
MYDBA@orcl >

Re: Select within a select? [message #409924 is a reply to message #409917] Wed, 24 June 2009 08:23 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'oh - totally failed to spot that this was another zombie thread.
Previous Topic: Convert CSV string of numbers
Next Topic: Using CLOB/BFILE to store payslips
Goto Forum:
  


Current Time: Sun Dec 04 08:46:18 CST 2016

Total time taken to generate the page: 0.07517 seconds