Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question pulling out extra field in slightly complex sql

    hi all

    System im working on is ASP with ACCESS database,

    Ive been workin on this query for an hour or two, and I am nearly there, but am hitting a brick wall in the last step,

    Here's what I got now (below), logic is all fine, the count(bindings_id) is pulling out the right number, but all I want is the bindings_name too, but if I ask for it I get "You tried to execute a query that does not include the specified expression 'bindings_name' as part of an aggregate function."

    I'm guessing this is something about how SQL isnt sure that the bindings_id and bindings_name are in a 1 to 1 relationship so it has a cry

    SELECT bindings_id, COUNT(bindings_id) FROM bindings, covers_consumer, (SELECT DISTINCT covers_links.covers_links_covers_consumer_id, covers_links_partners_type FROM covers_links) WHERE covers_links.covers_links_partners_type = 'wholesale' AND covers_links.covers_links_covers_consumer_id = covers_consumer.covers_consumer_id AND covers_consumer.covers_consumer_bindings_id = bindings.bindings_id GROUP BY bindings_id

    so anyway, anyone have an idea how I can do exactly the same thing but get the binding_name as well as the binding_id (and count of binding_id occurences for distinct cover types)

    thanks

    James

  • #2
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    the moron way

    well heres the brute force moron way.. anyone have anythin cleaner?

    SELECT * FROM (SELECT bindings_id, COUNT(bindings_id) FROM bindings, covers_consumer, (SELECT DISTINCT covers_links.covers_links_covers_consumer_id, covers_links_partners_type FROM covers_links) WHERE covers_links.covers_links_partners_type = 'wholesale' AND covers_links.covers_links_covers_consumer_id = covers_consumer.covers_consumer_id AND covers_consumer.covers_consumer_bindings_id = bindings.bindings_id GROUP BY bindings_id) AS bindingSet, bindings WHERE bindingSet.bindings_id = bindings.bindings_id

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    change:
    GROUP BY bindings_id
    to
    GROUP BY bindings_id, bindings_name

    in the original query. The error message is fairly self explanatory...


  •  

    Posting Permissions

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