I have a database of scholarships. My data is being saved over multiple tables: a scholarship table, a sponsor table (many-to-one relationship - a sponsor may offer many scholarships but a scholarship can only belong to one sponsor), 4 category tables and corresponding look-up tables for the 4 different categories -- eg., look-up tables store the id number of the scholarship linked to the id number of a category type.
I'm not exactly sure how to write the query I need to retrieve scholarship title and id, sponsor name. The one I do have shows below and isn't giving me the results I expect. It's not giving me an error, it just isn't right. I need the search to work like this:
If any items in category 1 (scholarship can be used to pay for grad school, undergrad school, etc.) are checked, include "AND category 1 = 'x AND y AND z'" (checked items in this category limit the results).
If any items in category 2 (scholarship can be used by x type of student -- adult re-entering college, part time students, etc.) are checked, include "AND category 2 = 'x AND y AND z'" (checked items in this category also limit the results).
If any items in category 3 (scholarship applies to a particular geographic location) are checked, include "AND category 3 = 'x OR y OR z'" (category 3 should add results -- you should always get records that don't have a specific geographic location as well as records that have are coded with the locations you checked in your search -- the more you checked, the larger your result set).
If any items in category 4 (scholarship applies to a particular background -- african american, women, child of veteran, etc.) are checked, include "AND category 4 = 'x OR y OR z'" (works like category 3 -- the more you check the more results you get).
Here's my query - based on a search with 2 items checked in each category. Do I need to use a join? or a sub-query? Or??? Appreciate any help/insight! Oh - and I'm using PHP v. 4.4.1 and MySQL v. 3.23.47. Thanks!
SELECT DISTINCT
scholarships.scholarship_title,
scholarships.scholarship_id,
sponsors.name
FROM
lookup_scholarship_category1,
lookup_scholarship_category2,
lookup_scholarship_category3,
lookup_scholarship_category4,
scholarships,
sponsors
WHERE
scholarships.sponsor_id = sponsors.sponsor_id
AND
lookup_scholarship_category1.scholarship_id = scholarships.scholarship_id
AND
lookup_scholarship_category2.scholarship_id = scholarships.scholarship_id
AND
lookup_scholarship_category3.scholarship_id = scholarships.scholarship_id
AND
lookup_scholarship_category4.scholarship_id = scholarships.scholarship_id
AND
lookup_scholarship_category1.type_id = '1 OR 2'
AND
lookup_scholarship_category2.type_id = '1 OR 2'
AND
lookup_scholarship_category3.type_id = '1 OR 2'
AND
lookup_scholarship_category4.type_id = '1 OR 2'
GROUP BY
scholarships.scholarship_title
This query gives me either just totally incorrect results, or just gives me all records in the datbase?
