select & order dilemma

Anon

Hello...
I have a SELECT question that is driving me crazy here. Here's the deal:

I have a main table named 'grants' that has many columns including 'deadline1', 'deadline2', 'deadline3', and 'deadline4'. All are dates. Every grant entry can have as many as four deadlines per year.

Until now, I have only had to search the grants and display the results ordered by the first deadline, deadline1. Now I am being asked to display the grants in order by date, but displaying each grant in the results once for each of its deadlines. If it has three deadlines, it would pop up once on 2002-01-01 ... 2002-07-01 ... 2002-12-01.

My SELECT statement before was:

SELECT * FROM grants WHERE name LIKE '$query%' ORDER BY $sortVar, deadline1 ASC

How could I change this to make it return each entry in the results one time per entry, regardless if it's returning each unique entry more than once. Or maybe there is a way to search the result array and re-order it to suit my needs? Does this make any sense to you at all? Thanks...

Andrew

mbraganca

Hi,

you don't say what database you are connected to. Using SQL with oracle, you could use the following:

select name, deadline1 from grants where name like '$query%'
union
select name, deadline2 from grants where name like '$qyery%'
union
....
order by $sortVar, 2 asc

Hope this helps

Mike B.

smook

Andrew...

I think it might help if you removed the deadlines from the grants table... mainly because it will normalize your data - as it stands now some of those dealine columns will be empty or never filled up.

It would also solve your dealine select problem because you could join the dealines and grants tables and it would spit out everything that matched (as many times as it matched).

e.g.

Grants

grant_id | data | blah....
1 big grant etc.
2 small grant etc.

Deadlines

deadline | grant_id
Jun 1 1
Aug 31 1
Nov 30 1
Feb 2 2
Nov 1 2
Dec 31 1

then if you wrote your select as:

select * from grants, deadlines where grants.grant_id = deadlines.grant_id order by $somesort, deadlines.deadline

you would get this:

deadline | grant_id | etc...
Feb 2 2
Jun 1 1
Aug 31 1
Nov 1 2
Nov 30 1
Dec 31 1

Bonus: You are not limited to 4 deadlines.

Warning: if you use that select, grants WITHOUT deadlines won't show. You'd have to do a OUTER join grants to deadlines to get that...

good luck!
sm

Anon

Oh boy oh boy oh boy.

It's working.

Wow. It took a little restructuring and re-writing my search algorithms, but now it's so much easier to manage. Brilliant. Thanks!