ruben- Posted September 11, 2007 Share Posted September 11, 2007 Database contains: tblBuildings: --------------- | buildingId | | 1 | | 2 | | 3 | --------------- tblBuildingsRequirements: ------------------------------------ | buildingId | requiredBuildingId | | 2 | 1 | | 3 | 1 | ------------------------------------ tblUsersBuildings ------------------------ | userid | buildingId | | 1 | 1 | ------------------------ Query I try to run: SELECT * FROM tblBuildings B WHERE ( SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR JOIN tblUsersBuildings UB ON UB.buildingId = BR.requiredBuildingId WHERE BR.buildingId = B.buildingId AND UB.userId = 1 ) = ( SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR WHERE BR.buildingId = B.buildingId ) What am I trying to do? Select all the buildings which requirements have been met, so this query *should* return: ---------------- | buildingId=1 | | buildingId=2 | | buildingId=3 | ---------------- But it returns: ---------------- | buildingId=1 | ---------------- My logic in my query: ;;; SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR ;;; JOIN tblUsersBuildings UB ON UB.buildingId = BR.requiredBuildingId ;;; WHERE BR.buildingId = B.buildingId ;;; AND UB.userId = 1 This part of the query should count how many buildings are required for the building "B.buildingId" Join it with the buildings that are actually build by user "1", and count how many there are left, so if a user built all the buildings, this count should say as many as the next count: ;;; SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR WHERE BR.buildingId = B.buildingId This part counts how many buildings are required by the building with id: B.buildingId Some testing: ;;; SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR ;;; JOIN tblUsersBuildings UB ON UB.buildingId = BR.requiredBuildingId ;;; WHERE BR.buildingId = 2 ;;; AND UB.userId = 1 Returns 1 ;;; SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR WHERE BR.buildingId = 2 Returns 1 So I really have no idea why it doesnt work in the real query.. WITHOUT THE COUNT() IT RETURNS: buildingId=2 buildingId=3 Quote Link to comment Share on other sites More sharing options...
ruben- Posted September 11, 2007 Author Share Posted September 11, 2007 My code works if I use: SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR WHERE BR.buildingId = B.buildingId AND EXISTS ( SELECT * FROM tblUsersBuildings UB WHERE UB.buildingId = BR.requiredBuildingId AND UB.userId = 1 ) in stead of: SELECT COUNT(BR.buildingId) FROM tblBuildingsRequirements BR JOIN tblUsersBuildings UB ON UB.buildingId = BR.requiredBuildingId WHERE BR.buildingId = B.buildingId AND UB.userId = 1 But if it's a join, and the row doesn't exist, it doesn't show up? Does it? So the results should be the same :\ Quote Link to comment Share on other sites More sharing options...
ruben- Posted September 11, 2007 Author Share Posted September 11, 2007 http://bugs.mysql.com/bug.php?id=27363 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.