I think something pretty close just came to me, the idea is you actually want a cartesion join of the student table and the software table, then figure out any results of that cartesian join that are not on the student_software table, kind of like this:
select
s.student_id,
t.software_id
from student s,
software t
where (s.student_id, t.software_id
not in select (student_id, software_id from
student_software)
You may need to work on it a little more, but I think this is the basic idea:
select
x.student_id,
a.student_name,
x.software_id,
b.software_name
from
(select
s.student_id,
t.software_id
from student s,
software t
where (s.student_id, t.software_id
not in select (student_id, software_id from
student_software)) x,
student a,
software b
where x.student_id=a.student_id and
x.software_id=b.software_id
-----Original Message-----
From: Brett N Exton [mailto:[Email address protected]
Sent: Thursday, March 30, 2006 9:34 AM
To: Kerber, Andrew
Subject: Re(2): Re(2): Re(2): SQL - Help !
No problem mate...It's puzzled a few people on this forum today and nobody has
yet totally solved it !
Something which seems so easy has really fried my brain today !
Cheers!
Brett
Kerber, Andrew (30/03/06 16:31):
>Well, I would have to think about it some more, that logic isnt quite right.
That logic will only get that software that no student has, we need a list for
each student. Its doable, but I dont know if I can figure it in my spare time
here at the office.
>
>-----Original Message-----
>From: Brett N Exton [mailto:[Email address protected]
>Sent: Thursday, March 30, 2006 9:19 AM
>To: Kerber, Andrew
>Subject: Re(2): Re(2): SQL - Help !
>
>
>I changed the script as you suggested but it now returns no rows:
>
>select
> s.student_name,
> q.student_id,
> q.software_id,
> q.software_desc
>from (select t.student_id, o.software_id, o.software_Desc
> from student_software t, software o
> where o.software_id = t.software_id (+)
> and t.software_id is null) q, student s
>where q.student_id = s.student_id
>
>/
>
>no rows selected
>
>
>My 3 tables:
>STUDENT:
>
>STUDENT_ID STUDENT_NAME
>---------- ------------
> 1 Brett
> 2 Phil
> 3 Chris
> 4 John
> 5 Tom
> 6 William
> 7 Peter
> 8 Daniel
> 9 Thomas
> 10 Kevin
>
>SOFTWARE:
>
>SOFTWARE_ID SOFTWARE_DESC
>----------- ----------------------
> 1111 Microsoft Word
> 2222 Microsoft Excel
> 3333 Microsoft Access
> 4444 Microsoft Powerpoint
> 5555 Microsoft Query
> 6666 CedAr Financials
> 7777 Expenses
> 8888 Time Recording
> 9999 Internet Explorer
>
>STUDENT_SOFTWARE:
>
>STUDENT_ID SOFTWARE_ID
>---------- -----------
> 1 1111
> 1 3333
> 1 9999
> 2 4444
> 2 8888
> 2 9999
> 4 1111
> 5 8888
> 5 1111
> 5 2222
> 6 2222
> 6 4444
> 6 9999
> 8 1111
> 8 2222
> 8 3333
> 8 9999
> 9 5555
> 9 3333
>
>I would have expected your SQL to return:
>
>SOFTWARE_ID SOFTWARE_DESC
>----------- ---------------------
> 2222 Microsoft Excel
> 4444 Microsoft Powerpoint
> 5555 Microsoft Query
> 6666 CedAr Financials
> 7777 Expenses
> 8888 Time Recording
>
>when run against student_id = 1
>
>Brett
>
>
>Kerber, Andrew (30/03/06 15:52):
>>It should b s.student_id
>>
>>-----Original Message-----
>>From: Brett N Exton [mailto:[Email address protected]
>>Sent: Thursday, March 30, 2006 8:49 AM
>>To: Kerber, Andrew
>>Subject: Re(2): SQL - Help !
>>
>>
>>**A LazyDBA.com subscriber has responded to your lazydba.com post**
>>**LazyDBA.com mail shield has forwarded you this email,
>>**and removed any attachments, and kept your email address secret
>>**from this person, and any viruses/trojans.
>>**If you reply to this email, the person will see your email address as normal
>>**Anything below this line is the original email text
>>
>>
>>Thanks for the reply Andrew!
>>
>>However I think you must have a bracketing issue because if I copy/paste your
>>SQL into my query window I get an error:
>>
>>ERROR at line 17:
>>ORA-00904: "T"."STUDENT_ID": invalid identifier
>>
>>to do with the last line
>>
>>I changed this to be a "hard coded" student_id which I have i.e. the last line
>>now reads:
>>
>>where q.student_id = 1
>>
>>but it returned no rows and this student definitely doesnt have every software
>>
>>Brett
>>
>>Kerber Andrew (30/03/06 15:41):
>>>This should be pretty close:
>>>
>>>select
>>> s.student_name,
>>> q.student_id,
>>> q.software_id,
>>> q.software_description
>>>from
>>>(select
>>> t.student_id,
>>> o.software_id,
>>> o.software_Description
>>>from
>>> student_software t,
>>> software o
>>>where o.software_id=t.software_id (+) and
>>> t.software_id is null) q,
>>>student s
>>>where q.student_id=t.student_id
>>>
>>>
>>>-----Original Message-----
>>>From: Brett N Exton
>>>[mailto:oracledba-ezmlmshield-x46347688.[Email address protected]
>>>Sent: Thursday, March 30, 2006 4:21 AM
>>>To: LazyDBA Discussion
>>>Subject: SQL - Help !
>>>
>>>
>>>Please help me with this brain teaser !
>>>
>>>I have 3 tables as below:
>>>
>>>TABLE: SOFTWARE
>>>SOFTWARE_ID
>>>SOFTWARE_DESCRIPTION
>>>
>>>TABLE: STUDENT
>>>STUDENT_ID
>>>STUDENT_NAME
>>>
>>>TABLE: STUDENT_SOFTWARE
>>>STUDENT_ID
>>>SOFTWARE_ID
>>>
>>>The requirement is an SQL statement which, for a specified student_id, will
>>>list all of the software from the SOFTWARE table which the student DOES NOT
>>>have.
>>>
>>>Brett
________________________________________________________________________
This e-mail and any attachments transmitted with it represents the
views of the individual(s) who sent them and should not be regarded
as the official view of Bridgend County Borough Council. The contents
are confidential and intended solely for the use of the addressee. If
you have received it in error, please inform the system administrator
on (+44) 01656 642111.
This e-mail and any attachments have been scanned with 'MessageLabs SkyScan' - http://www.messagelabs.com/
________________________________________________________________________
Maer'r e-bost hwn ac unrhyw atodiadau a drosglwddir gydag ef yn cynrychioli
safbwyntiau'r unigolyn (unigolion) a'u hanfonodd ac ni ddylid eu hystyried fel
safbwynt swyddogol Cyngor Bwrdeistref Sirol Pen-y-bont ar Ogwr. Mae'r cynnwy
syn gyfrinachol ac wedi'i fwriadu at ddefnydd y person y'i cyfeiriwyd ato yn
unig. Os ydych wedi ei dderbyn mewn camgymeriad, rhowch wybod i weinyddwry system ar (+44) 01656 642111.
Mae'r e-bost hwn ac unrhyw atodiadau wedi cael eu sganio gyda 'MessageLabs SkyScan' - http://www.messagelabs.com/
________________________________________________________________________
-----------------------------------------
CONFIDENTIALITY NOTICE
This message and any attachments are from the NAIC and are intended
only for the addressee. Information contained herein is
confidential, and may be privileged or exempt from disclosure
pursuant to applicable federal or state law. This message is not
intended as a waiver of the confidential, privileged or exempted
status of the information transmitted. Unauthorized forwarding,
printing, copying, distribution or use of such information is
strictly prohibited and may be unlawful. If you are not the
addressee, please promptly delete this message and notify the
sender of the delivery error by e-mail or by calling the NAIC Help
Desk at (816)783-8500.
Oracle LazyDBA home page