beer-monkey.com
|
| Posted: 03/28/2002, 1:18 AM |
|
I have used the following custom SQL to display a list of locations from one MySQL table, it then uses the 'left outer join' method to check another table (division_location) to see whether this location contains a certain division, if yes it flags it. This is then repeated for all divisions... which results in a list of all our locations, and by the side of each location a list of what divisions can be found there.
SELECT location.*, d1.division_id EDU, d2.division_id IND, d3.division_id OP, d4.division_id SA, d5.division_id TE, d6.division_id EX, d7.division_id HE, d8.division_id CO, d9.division_id AC, d10.division_id CC
FROM location
left outer join division_location d1 on location.location_id = d1.location_id and d1.division_id = 2
left outer join division_location d2 on location.location_id = d2.location_id and d2.division_id = 3
left outer join division_location d3 on location.location_id = d3.location_id and d3.division_id = 4
left outer join division_location d4 on location.location_id = d4.location_id and d4.division_id = 5
left outer join division_location d5 on location.location_id = d5.location_id and d5.division_id = 6
left outer join division_location d6 on location.location_id = d6.location_id and d6.division_id = 7
left outer join division_location d7 on location.location_id = d7.location_id and d7.division_id = 8
left outer join division_location d8 on location.location_id = d8.location_id and d8.division_id = 9
left outer join division_location d9 on location.location_id = d9.location_id and d9.division_id = 10
left outer join division_location d10 on location.location_id = d10.location_id and d10.division_id = 13
WHERE location.location_country = " & GetCountry & "
ORDER BY location.location
HOWEVER, what i would like to do is to add a search so that the user can select a DIVISION and see all LOCATIONS that contain THAT division. I can't just have a standard search box selecting division_id because division_id does not exist in the location table, it is aquired using the 'left outer join' from the division_location table.... PLEASE HELP as this is slowly driving me nuts...
|
|
|
 |
Alex Alexapolsky
|
| Posted: 03/28/2002, 5:31 AM |
|
1) Add a field to Search form and dont link it to table fields,
get this field value from Input variable in Grid/Open event ,
and modify sSQL variable containing sql statement to be executed so that
it would inlcude division_id
|
|
|
|
|
