SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Separating a subquery

    Im a newbie and dont know how to separate the below SQLpc string into 2 separate statements.

    I have about 5 of these statements in an ASP page for a poll im trying to get working.

    Im using MySQL 4.0.18 which apparently doesnt support nested queries. I get a 1064 ERROR.

    Can anyone help?

    <%
    Dim SelMax, ConnPoll, SQLpc, RSpc
    Set ConnPoll = Server.CreateObject("ADODB.Connection")
    ConnPoll.Open "driver= ###path to my DB###"
    SQLpc = "SELECT * FROM pollcount WHERE ID = (SELECT MAX(ID) FROM pollcount)"
    Set RSpc = ConnPoll.Execute(SQLpc)

    %>

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM pollcount ORDER BY ID DESC LIMIT 1

    returns the record with maximal ID

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thx stereofrog..dat worked good.

    However upon reading other mysql statements in the ASP page, i found they were getting the the ID from another table.

    ie.
    sqlq = "Select * from ques WHERE ID = (Select MAX(ID) From pollcount)"

    Do i simply add 2nd table ahead of ID like below?

    SELECT * FROM ques ORDER BY pollcount.ID DESC LIMIT 1

  4. #4
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I'm afraid it doesn't work this way, you'll need JOIN here. See Rewriting Subqueries as Joins in mysql manual.

    If have lots of subqueries like this, consider upgrading to mysql 4.1.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •