AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  simple sql query, but don't get it ...          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message Frank Ratzlow Guest Posted: Wed Feb 09, 2005 2:45 pm    Post subject: simple sql query, but don't get it ... Hi, very simple example. I've got 3 Tables STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZIN ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) I would like to know how the SQL query should look like if I want to know the following thing: =============== Find all magazins for a student with the id=42 that are not subscribed by him and are not of audience=3! =============== So let's say a certain biochemestry student has already subscribed to the biology mag but isn't interested in everything outside of his scope, he wants only a list related to his subject - in this case the "chemestry mag". Finally I need the EJB QL statement but translating it from the proper SQL shouldn't be that challenging ... TIA Frank [email]frank_ratzlow (AT) nojunk (DOT) hotmail.com[/email] Back to top kaeli Guest Posted: Wed Feb 09, 2005 3:06 pm    Post subject: Re: simple sql query, but don't get it ... In article <5ef29569.0502090645.1d8e958d (AT) posting (DOT) google.com>, [email]frank_ratzlow (AT) hotmail (DOT) com[/email] enlightened us with... Quote: STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZIN ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) I would like to know how the SQL query should look like if I want to know the following thing: =============== Find all magazins for a student with the id=42 that are not subscribed by him and are not of audience=3! So when's this due? ;) (and it's magazinE - with an 'E') (note: I think this is standard SQL, but I'm used to Oracle, so forgive me if it has any oracle specific syntax in it with the '!=') select MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id != 42 and MAGAZIN.audience != 3; -- -- ~kaeli~ A little rudeness and disrespect can elevate a meaningless interaction to a battle of wills and add drama to an otherwise dull day. http://www.ipwebdesign.net/wildAtHeart http://www.ipwebdesign.net/kaelisSpace Back to top Bjorn Abelli Guest Posted: Wed Feb 09, 2005 10:55 pm    Post subject: Re: simple sql query, but don't get it ... "kaeli" wrote... Quote: frank_ratzlow (AT) hotmail (DOT) com wrote... STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZIN ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) I would like to know how the SQL query should look like if I want to know the following thing: =============== Find all magazins for a student with the id=42 that are not subscribed by him and are not of audience=3! So when's this due? ;) (and it's magazinE - with an 'E') (note: I think this is standard SQL, but I'm used to Oracle, so forgive me if it has any oracle specific syntax in it with the '!=') select MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id != 42 and MAGAZIN.audience != 3; I suggest you use DISTINCT as well, as there can be several subscribing to the same magazin(e)s: select DISTINCT MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id <> 42 and MAGAZIN.audience <> 3; ....and yes, != is Oracle-specific, but you can use the standard SQL operator <> in Oracle as well... // Bjorn A Back to top Bjorn Abelli Guest Posted: Thu Feb 10, 2005 8:23 am    Post subject: Re: simple sql query, but don't get it ... "Bjorn Abelli" wrote... Quote: STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZIN ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) I would like to know how the SQL query should look like if I want to know the following thing: =============== Find all magazins for a student with the id=42 that are not subscribed by him and are not of audience=3! So when's this due? ;) (and it's magazinE - with an 'E') (note: I think this is standard SQL, but I'm used to Oracle, so forgive me if it has any oracle specific syntax in it with the '!=') select MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id != 42 and MAGAZIN.audience != 3; I suggest you use DISTINCT as well, as there can be several subscribing to the same magazin(e)s: select DISTINCT MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id <> 42 and MAGAZIN.audience <> 3; ...and yes, != is Oracle-specific, but you can use the standard SQL operator <> in Oracle as well... On the other hand, this solution only takes those magazines that someone else already is subscribing to, so if there are some of that type, this should be better: select title from MAGAZIN where audience <> 3 and id not in (select mag_id from SUBSCRIBE where stu_id = 42); // Bjorn A Back to top Frank Ratzlow Guest Posted: Thu Feb 10, 2005 9:54 am    Post subject: Re: simple sql query, but don't get it ... Hi guys, thanx for your answer, but unfortunately it isn't that easy... select DISTINCT MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id <> 42 and MAGAZIN.audience <> 3; The first join "SUBSCRIBE.mag_id=MAGAZIN.id" result is a list of all MAGAZINEs (sorry for the typo) that are already subscribed. BUT I want to know the mags that are _not_ subscribed. So from this starting resultset the final set of rows can only further restricted, not expanded. Now when I have all mags that are subscriped at all I sort out the subscripted mags from my student (id=42). Now I have all mags that are subscriped but not from the student I'm interested in. Finally all mags from these students are excluded that are math-mags. The result is that I have a list of non-math-mags that _other_ people have subscribed to. The problem is obviously with the join. Think, something like an outer join is required. But this is only a guess ... Any ideas? TIA Frank "Bjorn Abelli" <DoNotSpam.bjorn_abelli (AT) hotmail (DOT) com> wrote Quote: "kaeli" wrote... [email]frank_ratzlow (AT) hotmail (DOT) com[/email] wrote... STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZIN ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) I would like to know how the SQL query should look like if I want to know the following thing: =============== Find all magazins for a student with the id=42 that are not subscribed by him and are not of audience=3! So when's this due? ;) (and it's magazinE - with an 'E') (note: I think this is standard SQL, but I'm used to Oracle, so forgive me if it has any oracle specific syntax in it with the '!=') select MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id != 42 and MAGAZIN.audience != 3; I suggest you use DISTINCT as well, as there can be several subscribing to the same magazin(e)s: select DISTINCT MAGAZIN.title from MAGAZIN, SUBSCRIBE where SUBSCRIBE.mag_id=MAGAZIN.id and SUBSCRIBE.stu_id <> 42 and MAGAZIN.audience <> 3; ...and yes, != is Oracle-specific, but you can use the standard SQL operator in Oracle as well... // Bjorn A Back to top kaeli Guest Posted: Thu Feb 10, 2005 1:57 pm    Post subject: Re: simple sql query, but don't get it ... In article <5ef29569.0502100154.3c341ec (AT) posting (DOT) google.com>, [email]frank_ratzlow (AT) hotmail (DOT) com[/email] enlightened us with... Quote: Hi guys, thanx for your answer, but unfortunately it isn't that easy... Okay, one more try from me... :) STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZINE ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) Outer join to give a list of all magazines... select SUBSCRIBE.stu_id as stu_id, MAGAZINE.title as title, MAGAZINE.audience as audience from SUBSCRIBE, MAGAZINE where SUBSCRIBE.mag_id=MAGAZINE.id(+) Now you get something like stu_id | title | audience ------------------------------- 1 title1 1 2 title2 2 null title3 3 42 title1 2 So now you have a list of all magazines and, if they are subscribed to, which student has done so. From there, pull all mags that 42 has NOT subbed to that are NOT aud=3... select title from ( select SUBSCRIBE.stu_id as stu_id, MAGAZINE.title as title, MAGAZINE.audience as audience from SUBSCRIBE, MAGAZINE where SUBSCRIBE.mag_id=MAGAZINE.id(+) ) t1 where stu_id <> 42 and audience <> 3 How's that? -- -- ~kaeli~ Press any key to continue or any other key to quit http://www.ipwebdesign.net/wildAtHeart http://www.ipwebdesign.net/kaelisSpace Back to top Bjorn Abelli Guest Posted: Fri Feb 11, 2005 11:42 am    Post subject: Re: simple sql query, but don't get it ... "kaeli" <tiny_one (AT) NOSPAM (DOT) comcast.net> skrev i meddelandet news:MPG.1c75245c591b5e3298a2fa (AT) nntp (DOT) lucent.com... Quote: In article <5ef29569.0502100154.3c341ec (AT) posting (DOT) google.com>, [email]frank_ratzlow (AT) hotmail (DOT) com[/email] enlightened us with... Hi guys, thanx for your answer, but unfortunately it isn't that easy... Okay, one more try from me... :) STUDENT ======= id BIGINT (PK) firstname VARCHAR lastname VARCHAR SUBSCRIBE ========= stu_id BIGINT (FK to student.id) mag_id BIGINT (FK to magazin.id) MAGAZINE ======= id BIGINT (PK) title VARCHAR audience SMALLINT (can be of value 1, 2, 3) Outer join to give a list of all magazines... select SUBSCRIBE.stu_id as stu_id, MAGAZINE.title as title, MAGAZINE.audience as audience from SUBSCRIBE, MAGAZINE where SUBSCRIBE.mag_id=MAGAZINE.id(+) Now you get something like stu_id | title | audience ------------------------------- 1 title1 1 2 title2 2 null title3 3 42 title1 2 So now you have a list of all magazines and, if they are subscribed to, which student has done so. From there, pull all mags that 42 has NOT subbed to that are NOT aud=3... select title from ( select SUBSCRIBE.stu_id as stu_id, MAGAZINE.title as title, MAGAZINE.audience as audience from SUBSCRIBE, MAGAZINE where SUBSCRIBE.mag_id=MAGAZINE.id(+) ) t1 where stu_id <> 42 and audience <> 3 How's that? That's also an Oracle specific syntax (+), which I believe is *really* Oracle-specific, and from the datatypes I don't think the OP uses Oracle... Standard SQL uses LEFT JOIN and RIGHT JOIN to achieve those outer joins, which would be something like: select distinct title from (select SUBSCRIBE.stu_id as stu_id, MAGAZINE.title as title, MAGAZINE.audience as audience from MAGAZINE LEFT JOIN SUBSCRIBE ON SUBSCRIBE.mag_id=MAGAZINE.id) t1 where stu_id <> 42 and audience <> 3 Yesterday I posted a simpler solution, that would work without outer joins: select title from MAGAZIN where audience <> 3 and id not in (select mag_id from SUBSCRIBE where stu_id = 42); // Bjorn A Back to top kaeli Guest Posted: Fri Feb 11, 2005 1:32 pm    Post subject: Re: simple sql query, but don't get it ... In article <373k1fF55ra50U1 (AT) individual (DOT) net>, [email]DoNotSpam.bjorn_abelli (AT) hotmail (DOT) com[/email] enlightened us with... Quote: Yesterday I posted a simpler solution, that would work without outer joins: select title from MAGAZIN where audience <> 3 and id not in (select mag_id from SUBSCRIBE where stu_id = 42); That would fail in Oracle if there were more than 1000 mag_ids in the list. Do other DBMSs have that limitation? Thanks for the tips. -- -- ~kaeli~ Any sufficiently advanced technology is indistinguishable from magic. http://www.ipwebdesign.net/wildAtHeart http://www.ipwebdesign.net/kaelisSpace Back to top Bjorn Abelli Guest Posted: Fri Feb 11, 2005 11:04 pm    Post subject: Re: simple sql query, but don't get it ... "kaeli" wrote... Quote: Bjorn Abelli wrote... Yesterday I posted a simpler solution, that would work without outer joins: select title from MAGAZIN where audience <> 3 and id not in (select mag_id from SUBSCRIBE where stu_id = 42); That would fail in Oracle if there were more than 1000 mag_ids in the list. Do other DBMSs have that limitation? It's possible, but I don't know any with that limit besides Oracle. There's no mention of such a constraint on scalar subqueries in the documentation of standard SQL as far as I know. Quote: Thanks for the tips. You're welcome. // Bjorn A P.S. Oracle didn't conform to standard SQL with the syntax for outer joins until version 9i! Back to top kaeli Guest Posted: Mon Feb 14, 2005 2:33 pm    Post subject: Re: simple sql query, but don't get it ... In article <374s02F58utc4U1 (AT) individual (DOT) net>, [email]DoNotSpam.bjorn_abelli (AT) hotmail (DOT) com[/email] enlightened us with... Quote: // Bjorn A P.S. Oracle didn't conform to standard SQL with the syntax for outer joins until version 9i! Good to know. We're still on 8i, moving to 9i this year. I know, we're behind the times. I nag, they don't listen. Thanks again. -- -- ~kaeli~ Why do people who know the least know it the loudest? http://www.ipwebdesign.net/wildAtHeart http://www.ipwebdesign.net/kaelisSpace Back to top Frank Ratzlow Guest Posted: Tue Feb 15, 2005 2:55 pm    Post subject: Re: simple sql query, but don't get it ... Hi Bjorn, thanx for the answer. Seems that translating this query to the appropriate EJB-QL will be the next problem. Do you know if there is some way to translate a nested select to an ejb-ql? but many things for the solution (although I couldn't verify it yet ) cheers Frank "Bjorn Abelli" <DoNotSpam.bjorn_abelli (AT) hotmail (DOT) com> wrote Quote: "kaeli" wrote... Bjorn Abelli wrote... Yesterday I posted a simpler solution, that would work without outer joins: select title from MAGAZIN where audience <> 3 and id not in (select mag_id from SUBSCRIBE where stu_id = 42); That would fail in Oracle if there were more than 1000 mag_ids in the list. Do other DBMSs have that limitation? It's possible, but I don't know any with that limit besides Oracle. There's no mention of such a constraint on scalar subqueries in the documentation of standard SQL as far as I know. Thanks for the tips. You're welcome. // Bjorn A P.S. Oracle didn't conform to standard SQL with the syntax for outer joins until version 9i! Back to top Bjorn Abelli Guest Posted: Tue Feb 15, 2005 3:53 pm    Post subject: Re: simple sql query, but don't get it ... "Frank Ratzlow" wrote... Quote: thanx for the answer. Seems that translating this query to the appropriate EJB-QL will be the next problem. Do you know if there is some way to translate a nested select to an ejb-ql? Sure, look up the documentation... ;-) I believe most EJB-containers supports nested queries almost identical to standard SQL, although there might be dialectal differences between different vendors. Something like this "should" work... SELECT OBJECT(mag) FROM MagazinBean AS mag WHERE mag.audience <> 3 AND mag.id NOT IN (SELECT sub.mag_id FROM SubscribeBean AS sub WHERE sub.stu_id <> 3) // Bjorn A Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.