...

View Full Version : pulling out extra field in slightly complex sql



digijin
06-22-2006, 07:13 AM
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

digijin
06-22-2006, 08:24 AM
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

GJay
06-23-2006, 08:25 AM
change:
GROUP BY bindings_id
to
GROUP BY bindings_id, bindings_name

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum