7 Replies - 2419 Views - Last Post: 16 May 2006 - 03:25 AM Rate Topic: -----

#1 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Nesting SQL Queries in Frontpage

Posted 08 May 2006 - 08:46 AM

Please help - I have been searching the web for hours and have come up woith nothing :(

I am working in FrontPage 2003 and creating ASP pages which calls from and writes to an Access database.

I need to create a nested sql query and can't work it out at all. FrontPage's Database Results Wizrd is greyed out if I try to create a second database result inside another one.

Essentially I want to present a list in 2 levels - for each item in the main list I want to display one (or more or less) sub items which are uniquely assciated with that main list item.

I would have thought this would be simple enough and there must have been many people who have done this already?

Many thanks in advance for your help.

Is This A Good Question/Topic? 0
  • +

Replies To: Nesting SQL Queries in Frontpage

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Nesting SQL Queries in Frontpage

Posted 08 May 2006 - 09:36 AM

It is indeed simple enough...just not with FrontPage ;)

One of our members can likley write the SQL statement you require, but we'll need a little more information. Can you provide the schema of the tables and a description of what you want to get out?
Was This Post Helpful? 0
  • +
  • -

#3 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Re: Nesting SQL Queries in Frontpage

Posted 08 May 2006 - 10:13 AM

I have attached an image of the table structure.

The table is called Members. Essentially I want to list all members and and for each member list the other members where JAID is equal to MemberID.

Many thanks

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#4 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Re: Nesting SQL Queries in Frontpage

Posted 08 May 2006 - 10:30 AM

Sorry - my bad - attached is the .JPG image rather....

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#5 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Re: Nesting SQL Queries in Frontpage

Posted 09 May 2006 - 04:07 AM

I would really appreciate whatever help i can get on this - if you need more info please just advise....
Was This Post Helpful? 0
  • +
  • -

#6 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Re: Nesting SQL Queries in Frontpage

Posted 14 May 2006 - 04:25 PM

OK - Clearly not much help forthcoming from this quarter....
Was This Post Helpful? 0
  • +
  • -

#7 sigmazero13  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 10-May 06

Re: Nesting SQL Queries in Frontpage

Posted 15 May 2006 - 10:14 AM

Do you need it as a nested query, or can you do it using a Join?

Maybe something like this:

SELECT *ABC* FROM MEMBERS member1 LEFT JOIN MEMBERS member2 ON member1.MemberID = member2.JAID WHERE *XYZ* ORDER BY member1.MemberID;"

where
- ABC is the list of fields you want to return
- XYZ is the other criteria you want to filter on
- QRS is the ordering stuff (if applicable)

Then you just need the one loop to iterate, and maybe keep track of the "previous" member ID; when it changes, you know you are at the next person. IE, you could do a loop like this (pseudocode)
Dim memberRS, thisID, prevID
**open recordset into memberRS**
prevID = 0
do until memberRS.EOF
 thisID = memberRS("MemberID")
 if thisID = prevID then
  **print the new header info for a new member**
 end if
 **print the info for the right half of the join (the JAID info)**
 prevID = thisID
 memberRS.MoveNext
loop
memberRS.Close



I hope this helps.

This post has been edited by sigmazero13: 15 May 2006 - 10:15 AM

Was This Post Helpful? 0
  • +
  • -

#8 chuckaitch  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 06

Re: Nesting SQL Queries in Frontpage

Posted 16 May 2006 - 03:25 AM

Thanks a mill - i'll try it and get back to ya.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1