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
Hi, I wrote a short writeup on authenticating users in Active Directory here: http://blog.saush.com/?p=103 Hope this is useful. -- Sau Sheong 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
This is really good. Thanks much! On 7/18/06, Chang Sau Sheong <cssheong@pacific.net.sg> wrote:> > Hi, > > I wrote a short writeup on authenticating users in Active Directory here: > > http://blog.saush.com/?p=103 > > Hope this is useful. > > -- > Sau Sheong > > 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 > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060718/53e57e4b/attachment.html
Markus Kolb 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 -- Posted via http://www.ruby-forum.com/.
Julian Gall 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 Kolb 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 -- Posted via http://www.ruby-forum.com/.