Missing results and hHow to display a subset of a result set

[tinawina]

There's a lot of text here, but it goes fast and there is an SQL query below. Also posting table structure. Any help - very appreciated.

 

I am trying to display a sub-set of a result set and I can't figure out how to get it to work. The code below sort of works, but doesn't give me the correct results. It seems to skip some records, but not consistently?

 

Here's my table structure and my query. We are using MySQL v. 3.23.47.

 

scholarships table:

scholarship_id | scholarship title | sponsor id | deadline

 

sponsors table:

sponsor_id | sponsor name

 

fub table (funding may be used by these people):

fub_id | people

(eg., first year college students, part time students, etc.)

 

fuf table (funding may be used for these programs):

fuf_id | program

(eg., graduate programs, undergrad programs, etc.)

 

residential_requirement table (scholarship targets these locations):

residence_id | city

(eg., reno, los angeles, san francisco, etc.)

 

target_audiences table (scholarship targets these audiences):

audience_id | audience

(eg., veterans, women, first time college students, etc.)

 

lookup_scholarship_fuf table (links scholarship and fuf data)

scholarship_id | fuf_id

 

lookup_scholarship_fub table (links scholarship and fub data)

scholarship_id | fub_id

 

lookup_scholarship_audience table (links scholarship and audience data)

scholarship_id | audience_id

 

lookup_scholarship_residence table (links scholarship and residence data)

scholarship_id | residence_id

 

We want to set this up so that people first are presented with a search form that provides all of the residential_requirement and target_audiences options as check boxes. Submitting those selections brings up a result set that we then want to let people limit down further by selecting any/all options from the fub and/or fuf tables. that's where we're stuck.

 

We're able to get the first result set using the query below. We are able to display the fub and fuf data in the result page by doing a second and third query/while loop on their lookup tables based on the scholarship_id returned in the first result set.

 

But we can't figure out how to get just a sub-set of that result set based on fub and fuf selections. We'd like to show the result page and give people the option to "Narrow your results:" and then they check off their fub and fuf limiters.

I've tried including fub and fuf in the query below too - but that totally doesn't work, and has even brought down a 3rd party server. Part of the trouble, I think, is that the fub and fuf info will aways use AND, not or. Whereas audience and residence info will always use OR -- the more that applies to you, the more results you'll get.

 

Here's the query that works for the first part. Where do we go from here?? Any help with the second query, or changes to the first query to make this all happen in one shot - much appreciated!

 

SELECT

lookup_scholarship_residence.residence_id,

lookup_scholarship_audience.audience_id,

scholarships.scholarship_title,

scholarships.scholarship_id,

scholarships.deadline_day,

scholarships.deadline_month,

scholarships.deadline_year,

sponsors.org_name

FROM

lookup_scholarship_audience,

lookup_scholarship_residence,

scholarships,

sponsors

WHERE

scholarships.sponsor_id = sponsors.sponsor_id

AND

lookup_scholarship_audience.scholarship_id = scholarships.scholarship_id

AND

lookup_scholarship_residence.scholarship_id = scholarships.scholarship_id

GROUP BY

scholarships.scholarship_id

HAVING

lookup_scholarship_residence.residence_id = '1'

AND

lookup_scholarship_audience.audience_id = '1'

OR

// whatever checkboxes were selected for audience, eg:

lookup_scholarship_audience.audience_id = 'x' OR lookup_scholarship_audience.audience_id = 'y' OR

// whatever checkboxes were selected for residence, eg:

lookup_scholarship_residence.residence_id = 'x' OR

lookup_scholarship_residence.residence_id = 'y'

[fenway]

Yikes. A few things: first, you should be using a WHERE clause, not a HAVING clause. Second, you should be careful with your precedence of AND and OR clauses, since I don't see any parentheses. Third, an IN() clause combining all available options is much better optimized, not to mention easier to read. Fourth, you should use table aliases, especially with your long table names -- it will decrease the clutter a great deal.

 

BTW, the reason you're having trouble adding these "AND" clauses for your fuf/fub data is because you have it in a lookup table. Thus, even if the users checks off fub=A and fub=B, you can't simply write "AND fub='A' or fub='B'", because you want both conditions together ; but you also can't write "AND fub='A' and fub='B'", because that will always be false for any given row. You'd have to use a subquery to return any matching rows (or a temp. table if you don't have 4.1+).

 

Hope that helps.