Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: car's attributes should be >= spec's attributes (was "Help with Query")

    Hello all-

    I have a specification table that has some attributes defined.
    SpecId - Id of the specification
    Attribute - Attribute of the spec. (Like Color, HP etc)
    Value - Is the value of the attribute
    Then I have a car table that actually has information about the cars. Intention is to take each specification and match the cars that match the specification. If the car has more attributes than the spec, we ignore the extra attributes for the match. But if the car has less attributes, we don't even consider the car as a match (even if the attributes present, match). To summarize, the car's attributes should be >= spec's attributes.

    The code I have below is bad because I am joining the same tables twice. In addition, it fails in the condition "the car's attributes should be >= spec's attributes"

    Any help is greatly appreciated.




    DECLARE @Specification TABLE
    (SpecId VARCHAR(10),
    Attribute VARCHAR(100),
    Value VARCHAR(100))

    DECLARE @Car TABLE
    (CarName VARCHAR(10),
    Attribute VARCHAR(100),
    Value VARCHAR(100))

    INSERT INTO @Specification VALUES ('S1', 'Type', 'Sedan')
    INSERT INTO @Specification VALUES ('S1', 'Transmission', 'Auto')
    INSERT INTO @Specification VALUES ('S1', 'HP', '220')

    INSERT INTO @Specification VALUES ('S2', 'Type', 'SUV')
    INSERT INTO @Specification VALUES ('S2', 'Transmission', 'Manual')
    INSERT INTO @Specification VALUES ('S2', 'HP', '300')

    INSERT INTO @Car VALUES ('Accord', 'Type', 'Sedan')
    INSERT INTO @Car VALUES ('Accord', 'Transmission', 'Auto')
    INSERT INTO @Car VALUES ('Accord', 'HP', '220')
    INSERT INTO @Car VALUES ('Accord', 'Color', 'Black')

    INSERT INTO @Car VALUES ('Escape', 'Type', 'SUV')
    INSERT INTO @Car VALUES ('Escape', 'Transmission', 'Manual')
    INSERT INTO @Car VALUES ('Escape', 'HP', '300')

    INSERT INTO @Car VALUES ('Explorer', 'Type', 'SUV')
    INSERT INTO @Car VALUES ('Explorer', 'Transmission', 'Manual')

    SELECT DISTINCT Spec.SpecId, Car.CarName
    FROM @Specification Spec
    INNER JOIN @Car Car
    ON Spec.Attribute = Car.Attribute
    AND Spec.Value = Car.Value
    WHERE Spec.SpecId NOT IN (SELECT Spec.SpecId
    FROM @Specification Spec
    LEFT OUTER JOIN @Car Car
    ON Spec.Attribute = Car.Attribute
    AND Spec.Value = Car.Value
    WHERE Car.CarName IS NULL)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, there is nothing wrong with joining a table more than once in a query. It is pretty common, actually.

    Second, rephrase your requirement like this: "The car's matching attributes should be = spec's total attributes", and you'll get the same results but the query is easier to derive:

    select CarSpecMatches.CarName, CarSpecMatches.SpecId
    from
    (select Car.CarName, Specification.SpecId, count(*) Attributes
    from @Car Car
    inner join @Specification Specification
    on Car.Attribute = Specification.Attribute and Car.Value = Specification.Value
    group by Car.CarName, Specification.SpecId) CarSpecMatches
    inner join
    (select SpecID, count(*) Attributes
    from @Specification Specification
    group by SpecID) SpecAttributes
    on CarSpecMatches.SpecID = SpecAttributes.SpecID
    and CarSpecMatches.Attributes = SpecAttributes.Attributes
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Smile

    Blindman-

    Thanks for your help with the query. Your approach should be a lot better than mine. The main reason why I was concerned about joining the tables twice was that, as such these tables are very large and then my query was doing a left join versus inner.

    I will plug this into my code and see the results.

    I greatly appreciate your help with the query.

    Thanks

Posting Permissions

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