Hello.
I've got a tricky problem on my hands. It seems very easy, but I can't figure it out
anyway,
and I've already spent quite some time trying to solve it! I'm doing the whole thing
under
MySQL 4.0, which seems to be the real problem here! It doesn't support my query I
wrote! Well, I didn't run the query, but I know it'd never work!
Anyway, I'll get to the point. Here's a very simplified version of the tables I'm
working on.
Car
-----------------------------------------
| ID | IDBrand | IDModel | Year | Price |
-----------------------------------------
Characteristic
--------------------
| ID | Description |
--------------------
CarCharacteristics
--------------------
| IDCar | IDCharac |
--------------------
These tables can be described the following way:
Car
* contains cars
Characteristic
* contains various characteristics that can be applied to a car
- A/C, Winter Tyres, Electric Windows, ABS Brakes, etc.
CarCharacteristics
* indicates what characteristics the various cars actually have.
What I'd like to do, is to find which cars have a specific SET of characteristics. I'd
like to
be able to say which cars have the Characteristics 'X', 'Y' and 'Z' in ONE query. I'm
using
PHP to run the queries, but that isn't all that important, as I intend to let all the
processing
being done by the DBMS, or, alternatively, run various queries only passing the
resulting
identifiers directly in the new query.
For now, the only real implementation I have is an OR query, that is, I can find which
Cars have AT LEAST ONE of the characteristics in the desired set.
I'm capable of solving the problem with N queries for each car, but this kind of
solution
seems very time consuming for a database with even as little as a few thousands of
cars
with around a dozen, or more, characteristics each.
I believe that the following query would solve my problem, but unfortunately it's not
supported by MySQL MyISAM tables, and there are a couple of things about the query
that I don't know how to "translate" into a valid MySQL Query. I think that this query
would run just fine on an Oracle or DB2 system (maybe changing MINUS with EXCEPT,
but that's all).
SELECT A.*
FROM car as A
WHERE (
(
SELECT B.ID
FROM Characteristic AS B
WHERE B.ID IN('X','Y','Z')
MINUS
SELECT C.IDCharac
FROM CarCharacteristics AS C
WHERE C.IDCar=A.ID
) IS NULL
);
I appreciate any help in advance!
Remi Mikalsen
PS. I'm sorry if tables and other aligned elements don't appear correctly!
--
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL: http://www.iMikalsen.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
