cars attributes should be >= specs 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)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|||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

Capturing ALL elements and attributes

Hi
I need to capture and store the data from the following xml file:
- <server name="xxx-abcd">
- <match matchno="1">
<fullkey>99</fullkey>
<key>99</key>
<type>99</type>
<value>29</value>
</match>
- <match matchno="2">
<fullkey>87</fullkey>
<key>87</key>
<type>87</type>
<value>46</value>
</match>
- <match matchno="3">
<fullkey>52</fullkey>
<key>52</key>
<type>52</type>
<value>-12</value>
</match>
Using the following schema I can insert everything except the first
line <server name="xxx-abcd">
which I would like to do for each row so that it inserts the following
into my table
e.g
name matchno fullkey key type value
xxx-abcd 1 99 99 99 29
xxx-abcd 2 87 87 87 46
xxx-abcd 3 52 52 52 12
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- <xsd:element name="match" sql:relation="cpl">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="fullkey" type="xsd:integer" />
<xsd:element name="key" type="xsd:string" />
<xsd:element name="type" type="xsd:string" />
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="matchno" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
What changes do I need to make to my schema (above) to be able to do
this please?
Thanks in advance to anyone who replies!
Regards
ChrisAssume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
much easier way to achieve the same result with the XML data type .value()
and .nodes() methods.
<templeoakchris@.hotmail.com> wrote in message
news:1171569947.901469.306050@.j27g2000cwj.googlegroups.com...
> Hi
> I need to capture and store the data from the following xml file:
> - <server name="xxx-abcd">
> - <match matchno="1">
> <fullkey>99</fullkey>
> <key>99</key>
> <type>99</type>
> <value>29</value>
> </match>
> - <match matchno="2">
> <fullkey>87</fullkey>
> <key>87</key>
> <type>87</type>
> <value>46</value>
> </match>
> - <match matchno="3">
> <fullkey>52</fullkey>
> <key>52</key>
> <type>52</type>
> <value>-12</value>
> </match>
>
> Using the following schema I can insert everything except the first
> line <server name="xxx-abcd">
> which I would like to do for each row so that it inserts the following
> into my table
> e.g
> name matchno fullkey key type value
> xxx-abcd 1 99 99 99 29
> xxx-abcd 2 87 87 87 46
> xxx-abcd 3 52 52 52 12
>
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> - <xsd:element name="match" sql:relation="cpl">
> - <xsd:complexType>
> - <xsd:sequence>
> <xsd:element name="fullkey" type="xsd:integer" />
> <xsd:element name="key" type="xsd:string" />
> <xsd:element name="type" type="xsd:string" />
> <xsd:element name="value" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="matchno" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> What changes do I need to make to my schema (above) to be able to do
> this please?
> Thanks in advance to anyone who replies!
> Regards
> Chris
>|||On 15 Feb, 21:48, "Mike C#" <x...@.xyz.com> wrote:
> Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
> much easier way to achieve the same result with the XML data type .value()
> and .nodes() methods.
> <templeoakch...@.hotmail.com> wrote in message
> news:1171569947.901469.306050@.j27g2000cwj.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
Regards
Chris|||>
> Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
> Regards
> Chris
>
Sorry, it's been far too long since I did anything with SQLXML on 2000. I
did notice your XML is not well-formed (no closing </server> ). You might
want to look up the "sql:field" attribute at microsoft.com. You will
probably need to expand your XSD schema to include an outer "server" element
something like this (not tested):
...xsd:schema...
<xsd:element name="server">
<xsd:complexType>
<xsd:sequence>
...existing schema "match" element...
<xsd:attribute name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
You will probably need to play with the sql:relation and sql:field
attributes a bit as well.
Sorry couldn't be of more help.

Capturing ALL elements and attributes

Hi
I need to capture and store the data from the following xml file:
- <server name="xxx-abcd">
- <match matchno="1">
<fullkey>99</fullkey>
<key>99</key>
<type>99</type>
<value>29</value>
</match>
- <match matchno="2">
<fullkey>87</fullkey>
<key>87</key>
<type>87</type>
<value>46</value>
</match>
- <match matchno="3">
<fullkey>52</fullkey>
<key>52</key>
<type>52</type>
<value>-12</value>
</match>
Using the following schema I can insert everything except the first
line <server name="xxx-abcd">
which I would like to do for each row so that it inserts the following
into my table
e.g
name matchno fullkey key type value
xxx-abcd 1 99 99 99 29
xxx-abcd 2 87 87 87 46
xxx-abcd 3 52 52 52 12
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- <xsd:element name="match" sql:relation="cpl">
- <xsd:complexType>
- <xsd:sequence>
<xsd:element name="fullkey" type="xsd:integer" />
<xsd:element name="key" type="xsd:string" />
<xsd:element name="type" type="xsd:string" />
<xsd:element name="value" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="matchno" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
What changes do I need to make to my schema (above) to be able to do
this please?
Thanks in advance to anyone who replies!
Regards
Chris
Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
much easier way to achieve the same result with the XML data type .value()
and .nodes() methods.
<templeoakchris@.hotmail.com> wrote in message
news:1171569947.901469.306050@.j27g2000cwj.googlegr oups.com...
> Hi
> I need to capture and store the data from the following xml file:
> - <server name="xxx-abcd">
> - <match matchno="1">
> <fullkey>99</fullkey>
> <key>99</key>
> <type>99</type>
> <value>29</value>
> </match>
> - <match matchno="2">
> <fullkey>87</fullkey>
> <key>87</key>
> <type>87</type>
> <value>46</value>
> </match>
> - <match matchno="3">
> <fullkey>52</fullkey>
> <key>52</key>
> <type>52</type>
> <value>-12</value>
> </match>
>
> Using the following schema I can insert everything except the first
> line <server name="xxx-abcd">
> which I would like to do for each row so that it inserts the following
> into my table
> e.g
> name matchno fullkey key type value
> xxx-abcd 1 99 99 99 29
> xxx-abcd 2 87 87 87 46
> xxx-abcd 3 52 52 52 12
>
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> - <xsd:element name="match" sql:relation="cpl">
> - <xsd:complexType>
> - <xsd:sequence>
> <xsd:element name="fullkey" type="xsd:integer" />
> <xsd:element name="key" type="xsd:string" />
> <xsd:element name="type" type="xsd:string" />
> <xsd:element name="value" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="matchno" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> What changes do I need to make to my schema (above) to be able to do
> this please?
> Thanks in advance to anyone who replies!
> Regards
> Chris
>
|||On 15 Feb, 21:48, "Mike C#" <x...@.xyz.com> wrote:
> Assume this is SQL 2000 and SQLXML you're using? If on SQL 2005 there's a
> much easier way to achieve the same result with the XML data type .value()
> and .nodes() methods.
> <templeoakch...@.hotmail.com> wrote in message
> news:1171569947.901469.306050@.j27g2000cwj.googlegr oups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
Regards
Chris
|||>
> Oops... yes, I am using SQL 2000 and SQLXML ... any ideas?
> Regards
> Chris
>
Sorry, it's been far too long since I did anything with SQLXML on 2000. I
did notice your XML is not well-formed (no closing </server>). You might
want to look up the "sql:field" attribute at microsoft.com. You will
probably need to expand your XSD schema to include an outer "server" element
something like this (not tested):
...xsd:schema...
<xsd:element name="server">
<xsd:complexType>
<xsd:sequence>
...existing schema "match" element...
<xsd:attribute name="name" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
You will probably need to play with the sql:relation and sql:field
attributes a bit as well.
Sorry couldn't be of more help.