# SQL query question **URL:** \ **Category:** Rails\ **Created:** [July 18, 2006, 5:56pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859 "2006-07-18T17:56:26Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 18, 2006, 5:56pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/1 "2006-07-18T17:56:26Z")
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 ---
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 18, 2006, 7:40pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/2 "2006-07-18T19:40:57Z")
This is really good. Thanks much! ---
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 18, 2006, 6:05pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/3 "2006-07-18T18:05:48Z")
Hi, I wrote a short writeup on authenticating users in Active Directory here: [http://blog.saush.com/?p=103](http://blog.saush.com/?p=103) Hope this is useful. – Sau S. [http://blog.saush.com](http://blog.saush.com) - brain dump [http://www.projectible.com](http://www.projectible.com) - online project publishing [http://jaccal.sourceforge.net](http://jaccal.sourceforge.net) - smart card toolkit [http://screensvr.rubyforge.org](http://screensvr.rubyforge.org) - Flickr screensaver ---
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 19, 2006, 12:48pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/4 "2006-07-19T12:48:48Z")
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 ---
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 18, 2006, 10:30pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/5 "2006-07-18T22:30:54Z")
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 ---
**Author:** ![subroutine](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@subroutine](https://www.ruby-forum.com/u/subroutine)\ **Post date:** [July 19, 2006, 1:52pm UTC](https://www.ruby-forum.com/t/sql-query-question/98859/6 "2006-07-19T13:52:02Z")
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