Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Retrieve data from another table in subquery

    Hi there, here is my current query:

    sqlTemp = "SELECT id,code,description FROM tbl_content " &_
    "WHERE EXISTS " &_
    "(SELECT * FROM tbl_published_content " &_
    "WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"


    I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.

    Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?

    So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?

    thanks a million!

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Try this instead
    select
    [id]
    ,code
    ,description
    from tbl_content tc join
    tbl_published_content tpc
    on tc.[id] = tpc.[id]
    order by tpc.ncorrectorder


    now the correct way to do this is to create a stored procedure
    then execute the procedure from your client
    your clients will thank you.

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Great! thanks

    I forgot to mention it was for a JetSQL database, so here is the final query if anyone is interested

    sqlTemp = "SELECT tc.id,tc.code,tc.description,tpc.nCorrectOrder FROM tbl_content tc INNER JOIN " &_
    "tbl_published_content tpc on tc.id = tpc.id " &_
    "ORDER BY tpc.nCorrectOrder"

    Best regards, Eth.
    Last edited by Ethan21; 04-11-04 at 07:45.

Posting Permissions

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