Hey
Try this instead:
select
st.student_id,
sw.software_id
from student st, software sw
where
NOT exists (Select * from student_software ss where ss.software_id = sw.software_id and ss.student_id = st.student_id);
// Martin
-----Original Message-----
From: Brett N Exton [mailto:[Email address protected]
Sent: den 31 mars 2006 14:20
To: Martin Eklund
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
I've tried your suggestion but got an error:
sw.software_id = ss.software_id (+)
*
ERROR at line 6:
ORA-01417: a table may be outer joined to at most one other table
Thanks
Brett
Martin Eklund (31/03/06 13:14):
>Heres my way of thinking. A "wild join" between student and software would get
all software for all students. Then simply left join student_software and
filter where student_software.software_id is null:
>
>Select
> st.student_id,
> sw.software_id
>from software sw, student st, student_software ss
>where
> sw.software_id = ss.software_id (+)
> and st.student_id = ss.student_id (+)
> and ss.software_id IS NULL;
>
>
>// Martin
>
>-----Original Message-----
>From: Kerber Andrew
>[mailto:oracledba-ezmlmshield-x40749061.[Email address protected]
>Sent: den 30 mars 2006 17:47
>To: LazyDBA Discussion
>Subject: RE: Re(2): Re(2): Re(2): SQL - Help !
>
>
>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/
________________________________________________________________________
MS Sql Server LazyDBA home page