Hello,
I know it’s off-topic. But I’m sure you are using SQL and can help me 
I’ve a table CARS and a table KEYS and a LOCKS table.
CARS
id|name
1|audi
2|ford
3|mazda
4|porsche
…
KEYS
id|car_id|lock_id
1|1|1
2|2|1
3|2|2
4|3|1
5|3|2
6|4|1
7|4|2
8|4|3
…
LOCKS
id|name
1|main
2|spare
3|engine
…
A car can have many keys. Keys are for different locks.
How can I get the cars which have at least the keys for locks like
another car.
So in the above example…
ford and mazda have keys for the same locks. So a query for mazda or
ford should return ford, mazda and porsche. audi is no result because it
misses the key for lock 2. porsche is a result because it has all keys
mazda or ford has plus a third key.
A query for audi should return all four because all cars in the example
have keys for lock 1 which is the only key audi has.
A query for porsche should only return porsche because it is the only
car which has the key for lock 3.
It is enough to get car_id results and the car_id for the car to query
is known.
Is it possible to handle a multiple row condition in SQL?
Markus
This is really good. Thanks much!
Hi,
I wrote a short writeup on authenticating users in Active Directory
here:
http://blog.saush.com/?p=103
Hope this is useful.
–
Sau S.
http://blog.saush.com - brain dump
http://www.projectible.com - online project publishing
http://jaccal.sourceforge.net - smart card toolkit
http://screensvr.rubyforge.org - Flickr screensaver
Julian G. wrote on 18.07.2006 22:30:
[…]
SELECT c1.name, k1.car_id
FROM keys AS k1
JOIN keys AS k2 ON k1.lock_id = k2.lock_id
JOIN cars AS c1 ON c1.id = k2.car_id
JOIN cars AS c2 ON k1.car_id = c2.id
WHERE c2.name = ‘mazda’
GROUP BY name
HAVING COUNT() = (SELECT COUNT() FROM keys WHERE car_id = k1.car_id)
Many thanks Julian.
It works… but the group by needs to be c1.name I think.
the same number of keys as the car you are interested in. The k1.car_id
in the SELECT is necessary, it seems, in order for it to be used in the
SELECT COUNT(*).
Is this k1.car_id in the main select really necessary?
I’ve tested it here and c1.name alone has the same behavior. Are there
DBs which need a id/integer column for counting?
Markus
Markus K. wrote:
How can I get the cars which have at least the keys for locks like
another car.
So in the above example…
ford and mazda have keys for the same locks. So a query for mazda or
ford should return ford, mazda and porsche. audi is no result because it
misses the key for lock 2. porsche is a result because it has all keys
mazda or ford has plus a third key.
A query for audi should return all four because all cars in the example
have keys for lock 1 which is the only key audi has.
A query for porsche should only return porsche because it is the only
car which has the key for lock 3.
SELECT c1.name, k1.car_id
FROM keys AS k1
JOIN keys AS k2 ON k1.lock_id = k2.lock_id
JOIN cars AS c1 ON c1.id = k2.car_id
JOIN cars AS c2 ON k1.car_id = c2.id
WHERE c2.name = ‘mazda’
GROUP BY name
HAVING COUNT() = (SELECT COUNT() FROM keys WHERE car_id = k1.car_id)
Amend the name in the WHERE condition to see the results you wanted.
To explain briefly, JOIN c2 selects the cars record by name using the
WHERE condition. JOIN c1 is there to give you car name (c1.name) in the
result. JOINing the keys table with itself gets all the key records that
match any of the key records for the car you’re interested in. The GROUP
BY / HAVING is to ensure that you only return records where a car has
the same number of keys as the car you are interested in. The k1.car_id
in the SELECT is necessary, it seems, in order for it to be used in the
SELECT COUNT(*).
Julian
Markus K. wrote:
It works… but the group by needs to be c1.name I think.
You’re right but in MySQL, at least, it seems to work without the c1. It
must somehow default to the first cars table but it does seem odd, I
agree.
Is this k1.car_id in the main select really necessary?
Again, this may be a MySQL thing. If I remove this, MySQL complains that
the k1.car_id in the final SELECT is an “unknown column”.
FYI, my MySQL version is 4.1.9.
Julian
