Jump to content


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

  • Please log in to reply
1 reply to this topic

#1 tinawina

  • Members
  • Pip
  • Newbie
  • 1 posts

Posted 11 January 2006 - 06:40 PM

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!

scholarships.sponsor_id = sponsors.sponsor_id
lookup_scholarship_audience.scholarship_id = scholarships.scholarship_id
lookup_scholarship_residence.scholarship_id = scholarships.scholarship_id
lookup_scholarship_residence.residence_id = '1'
lookup_scholarship_audience.audience_id = '1'
// 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'

#2 fenway

  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 January 2006 - 07:22 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users