Re: SQL Query Question: Avoiding Subqueries

• Previous message: Bankim Desai: "Re: Grid Query"
• In reply to: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Next in thread: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Reply: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 8 Dec 2004 08:02:30 +0100

Ken,

if your problem is the number of subqueries and joins, you might consider
waiting a few months for VFP9. The number of join's and subqueriers have
been enhanced.
Read more about it here:
http://code-magazine.com/focus/Article.aspx?quickid=0404022

I'm sure you know that you can download the public beta here:
http://msdn.microsoft.com/vfoxpro/

--
Cheers
Carsten
_______________________________
"Ken Dibble" <balderdash@spongemop.com> schrieb im Newsbeitrag
news:q3mcr0hbiahqbjk7kj0at310ooh17btt1b@4ax.com...
> Thanks very much John!
>
> Of course, I may still be up a sort of creek here.
>
> My goal is to create a "query wizard" that lets people pick and choose
> from various fields and have the SQL syntax at least partially
> generated (they can then tweak it manually if needed, though in some
> applications it will have to produce foolproof simple queries without
> any editing, or even viewing, of the generated code).
>
> In the ideal case, this thing would be totally meta-data driven; I
> won't know in advance what fields and tables might be available to
> pick from. In such a case I can't rely on a brute force catalog of all
> possible SQL SELECT statements. So I was looking for a few general
> "templates" that should handle the majority of cases. The two
> solutions we have here suffer from being quite long; also, at least in
> VFP 7, where I'm working, I think there's a limit on the number of
> JOINs that can be used. (OTOH, I think there's also a limit on the
> number of subqueries.)
>
> Your solution seems simpler and easier to use as a template. And I
> realize that I might have to forego a completely data-driven approach
> and rely on a catalog, but I'd like to see if there is a more elegant
> solution.
>
> Any thoughts?
>
> Thanks very much again.
>
> Ken
>
> On Tue, 7 Dec 2004 14:58:32 -0700, "John Spiegel"
> <jspiegel@YETANOTHERSPAMHATERc-comld.com> wrote:
>
> >Hey Ken,
> >
> >Thanks for supplying the sample data!  Makes us much more happy to help.
> >
> >One option would be to join into the same table multiple times...
> >
> >SELECT DISTINCT People.* ;
> >    FROM People JOIN Services Housing ON People.TheID = Housing.PeoID AND
;
> >            Housing.ServName = "HOUSING";
> >        JOIN Services Counseling ON People.TheID = Counseling.PeoID AND ;
> >            Counseling.ServName = "COUNSELING"
> >
> >HTH,
> >
> >John
> >"Ken Dibble" <balderdash@spongemop.com> wrote in message
> >news:tr6cr0hkt9ummr0kr2nfjtk476tv39rap8@4ax.com...
> >> SQL Query Question:
> >>
> >> CREATE CURSOR people ( ;
> >>      theid I, ;
> >>      lastname C(35) )
> >>
> >> INSERT INTO people (theid, lastname) ;
> >> VALUES (1,"Washington")
> >>
> >> INSERT INTO people (theid, lastname) ;
> >> VALUES (2,"Adams")
> >>
> >> INSERT INTO people (theid, lastname) ;
> >> VALUES (3,"Jefferson")
> >>
> >> SELECT 0
> >>
> >> CREATE CURSOR services ( ;
> >>      theid I, ;
> >>     peoid I, ;
> >>      servname C(35) )
> >>
> >> INSERT INTO services (theid, peoid, servname) ;
> >> VALUES (1, 1, "HOUSING")
> >>
> >> INSERT INTO services (theid, peoid, servname) ;
> >> VALUES (2, 1, "COUNSELING")
> >>
> >> INSERT INTO services (theid, peoid, servname) ;
> >> VALUES (3, 2, "HOUSING")
> >>
> >> INSERT INTO services (theid, peoid, servname) ;
> >> VALUES (4, 2, "COUNSELING")
> >>
> >> INSERT INTO services (theid, peoid, servname) ;
> >> VALUES (5, 3, "COUNSELING")
> >>
> >> I want to do a SQL SELECT that gives me the PK and last name, just
> >> once, of all persons who have both a services record for housing and a
> >> services record for counseling. With the above data, the query should
> >> return the following:
> >>
> >> theid                lastname
> >> 1                    Washington
> >> 2                    Adams
> >>
> >> I know the following query will do what I want:
> >>
> >> SELECT people.theid, people.lastname DISTINCT FROM people, services ;
> >> WHERE people.theid IN (SELECT services.peoid FROM services WHERE
> >> services.servname == "HOUSING") ;
> >> AND people.theid IN (SELECT services.peoid FROM services WHERE
> >> services.servname == "COUNSELING") ;
> >> INTO CURSOR temp
> >>
> >> My question is:
> >>
> >> Is there a JOIN or other syntax that would give me the same results
> >> without the subqueries?
> >>
> >> TIA,
> >>
> >> Ken Dibble
> >>
> >
>

• Previous message: Bankim Desai: "Re: Grid Query"
• In reply to: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Next in thread: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Reply: Ken Dibble: "Re: SQL Query Question: Avoiding Subqueries"
• Messages sorted by: [ date ] [ thread ]