Mark L. Breen
subs at solution-providers.ie
Sun May 11 03:06:47 CDT 2003
Hello All, I presume that all the above discussions are talking about Jet. I have been using these kind of SQL nested / embedded queries for quite a while in SQL Server / MSDE. They are brilliant. You can write a stored proc that does all sorts of SQL checking, you can nest you selects in Insert statements, and have nested selects within selects that perform inserts. You can also do things like If (a whole load of selects and nested selects) some condition then (a whole lot more updates/ inserts or plain ol' selects) else (more again updates/ inserts or plain ol' selects) Since I started using nested selects, I have no need for temp tables or even virtual tables, If I had to do the work with 10's of 1000' of records, I would give some consideration to performance, but even still, I guess and vtable vs a real table vs a nested select must have at least comparable performance results. If you have not used then guys, take a look, I think that they are great FWIW. Enjoy Mark ----- Original Message ----- From: "John Bartow" <john at winhaven.net> To: <accessd at databaseadvisors.com> Sent: Thursday, May 08, 2003 4:13 PM Subject: RE: RE: [AccessD] SQL in-line subquery > I read about this syntax in Access Advisor a couple of years ago. It was > something a user wrote in for a tip. Ken Getz (tips editor) had the same > reaction that everyone else seems to have - how did you figure this out?! He > couldn't find it documented anywhere. > > I pulled it out from my collection a couple of months ago to review it and > now I can't find that issue! When I find it again I will share it and the > author's name. > > JB > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Wortz, Charles > > Sent: Thursday, May 08, 2003 9:01 AM > > To: accessd at databaseadvisors.com > > Subject: RE: RE: [AccessD] SQL in-line subquery > > > > > > Dave, > > > > I have to agree with you. From the article you cited, we can see how to > > write regular subqueries. So why the special []. syntax exists, and > > what does it do that the regular subquery cannot do, still has not been > > explained. > > > > Maybe somebody that is between projects will take the time to > > investigate what []. does and will report back to us. Many-to-Many is > > always looking for good articles. > > > > Charles Wortz > > Software Development Division > > Texas Education Agency > > 1701 N. Congress Ave > > Austin, TX 78701-1494 > > 512-463-9493 > > CWortz at tea.state.tx.us > > > > > > > > -----Original Message----- > > From: davesharpe2 at cox.net [mailto:davesharpe2 at cox.net] > > Sent: Thursday 2003 May 08 08:41 > > To: accessd at databaseadvisors.com > > Subject: Re: RE: [AccessD] SQL in-line subquery > > I > > Charles, I agree. > > > > One thing I would like to note that I'm not > > sure that I see the significance of the > > "[ ]. syntax" ( while I agree that it works ). > > > > I get the same results using the following > > two queries. ( I regret that my client word- > > wrapped the examples ) > > > > ================================ > > 1 - Using [ ]. > > SELECT > > Count(*) AS DaysUnique > > FROM > > [SELECT DISTINCT po_date FROM LAW70_PURCHORDER]. AS subDistinctDays > > > > 2 - Using a normal "SubQuery in the From Clause" > > SELECT > > Count(*) AS DaysUnique > > FROM > > (SELECT DISTINCT po_date FROM LAW70_PURCHORDER) AS subDistinctDays > > > > Dave > > > > > > > > From: "Wortz, Charles" <CWortz at tea.state.tx.us> > > > Date: 2003/05/08 Thu AM 08:20:49 EDT > > > To: <accessd at databaseadvisors.com> > > > Subject: RE: [AccessD] SQL in-line subquery > > > > > > Dave, > > > > > > Nice article, but it does not discuss the specific type of subquery > > > that is the subject of the original questioner; namely, the [ ]. > > > syntax for subqueries. > > > > > > > > > Charles Wortz > > > -----Original Message----- > > > From: dave sharpe [mailto:davesharpe2 at cox.net] > > > Sent: Wednesday 2003 May 07 21:42 > > > To: accessd at databaseadvisors.com > > > Subject: Re: [AccessD] SQL in-line subquery > > > > > > > > > Friends I found this on the subject and thought that > > > it might be of benefit to some members of the group. > > > > > > http://www.nesug.org/Proceedings/nesug98/dbas/p005.pdf > > > > > > Dave > > > > > > > > > ----- Original Message ----- > > > From: Wortz, Charles <mailto:CWortz at tea.state.tx.us> > > > To: accessd at databaseadvisors.com > > > Sent: Wednesday, May 07, 2003 12:45 PM > > > Subject: RE: [AccessD] SQL in-line subquery > > > > > > Also, about a year ago this subject was brought up with about > > the > > > same > > > amount of information documented. > > > > > > Charles Wortz > > > -----Original Message----- > > > From: Charlotte Foust [mailto:cfoust at infostatsystems.com] > > > Sent: Wednesday 2003 May 07 11:16 > > > To: accessd at databaseadvisors.com > > > Subject: RE: [AccessD] SQL in-line subquery > > > > > > But, Gustav, we just created references to it here! <VBG> > > > > > > Charlotte Foust > > > > > > -----Original Message----- > > > From: Gustav Brock [mailto:gustav at cactus.dk] > > > Sent: Tuesday, May 06, 2003 11:51 PM > > > To: accessd at databaseadvisors.com > > > Subject: Re: [AccessD] SQL in-line subquery > > > > > > > > > Hi Charlotte > > > > > > > .. If you ever find it in help, for goodness sake, post the > > > reference > > > > > > > here! > > > > > > I would say: if you ever find any reference to this, post it > > here. > > > > > > /gustav > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >