SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    0 Post(s)
    0 Thread(s)

    Trying to write a two-part fast query, quite stuck...

    I am attempting to execute two queries that run as fast as humanly possible... to be honest, I'm having a really hard time even getting started with it. I'm generally decent with SQL but for some reason this one is eluding me in terms of doing it within a single query, which would help my application's speed. There are two queries that need be performed, which are related.

    The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?"

    The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3.

    And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.

    Thanks so much for your help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Toronto, Canada
    63 Post(s)
    3 Thread(s)
    first query:
    SELECT objects.object_name
      FROM objects 
      JOIN ( SELECT object_id
               FROM properties
              WHERE property_name = 'green'  AND score > 3
                 OR property_name = 'hot'    AND score < 2
                 OR property_name = 'bright' AND score > 0
                 BY object_id
             HAVING COUNT(*) = 3 
           ) AS okay
        ON okay.object_id = objects.object_id
    second query i did not understand | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Posting Permissions

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